SQLTeam.com | Weblogs | Forums

Converting a time field in SQL to date and time


#1

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.


#2

If it's a "time" data type, no date is stored in the data. Do you have a different column with the date?


#3

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?


#4


#5

Default date is 0, which is 1900-01-01.


#6

But the date suppose to be like 1/3/1997, 3/4/1989 and so on...


#7

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

#8

Thanks I'll try this.
Pasi


#9

So this is what I am getting:


#10

Sorry, try this:

CONVERT(datetime, SESSION_START_DT) + CONVERT(datetime, SESSION_START_TIME)

#11

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


#12

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.


#13

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?


#14

I hope I can have them do this. helps a lot.
Pasi