Hi I have a field which is a TIME , how can can I convert this to get the date and time?
this field called session_start time and it shows in db as ---> 13:30:00.0000000
any way I can convert this to show me the date as well like 1/2/1997 13:30:00.0000000 ?
Thanks.
If it's a "time" data type, no date is stored in the data. Do you have a different column with the date?
Ok here is my query:
,convert(varchar(10),SESN.SESSION_START_TIME, 108) test_SESSION_START_TIME
output:13:30:00.0000000
when I convert it gives me the date but its all in 1900?
,CAST(SESN.SESSION_START_TIME AS DATETIME) SESSION_START_TIME
output: 1900-01-01 13:30:00.000
when I convert it gives me the date but its all in 1900?
Default date is 0, which is 1900-01-01.
But the date suppose to be like 1/3/1997, 3/4/1989 and so on...
Assuming that datatypes are SESSION_START_DT is DATE and SESSION_START_TIME is TIME then this perhaps:
CONVERT(datetime, SESSION_START_DT) + SESSION_START_TIME
Thanks I'll try this.
Pasi
Sorry, try this:
CONVERT(datetime, SESSION_START_DT) + CONVERT(datetime, SESSION_START_TIME)
Actually that isn't going to work in the WHERE clause.
Your SESSION_START_DT is a date, comparing it to SESSION_START_DT + SESSION_START_TIME is never going to match, unless SESSION_START_TIME is 00:00
Perhaps you meant something else?
Either way, if you convert both the DATE and TIME datatype objects to DATETIME then you can add them together to get a composite DATETIME object
Thanks its not working for me and I think I know why because the session_start_time in Oracle is a "date" field and has date in it, when they converted it to SQL its "time" which is not correct.
in SQL shows as 13:30:00.0000000
in oracle shows as 1/1/1997
Pasi.
I guess that's the problem. I presume they looked at the column name and assumed it was a TIME rather than a DATE.
Can that be fixed, and the data re-converted / re-imported?
I hope I can have them do this. helps a lot.
Pasi