SQLTeam.com | Weblogs | Forums

Converting a time field in SQL to date and time


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 ?


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

when I convert it gives me the date but its all in 1900?

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:



Thanks I'll try this.


So this is what I am getting:


Sorry, try this:



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


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.