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.