need to create date from other col. values?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

need to create date from other col. values??

Author

Topic

raritan
Starting Member

39 Posts

Posted - 2007-07-10 : 16:26:09

I have a table that contains columns for the century_value, the year_value, the month_value, the day_value, the hour_value, the minute_value, the second_value, and the time_of_day_value of a date, rather than a date column.

I am going to add a date column, and I need a function to populate each record with a date based on the values in the above referenced columns.

for example one record has the following data:

century_value = 20
year_value = 7
month_value = 1
day_value = 12
hour_value = 1
minute_value = 17
second_value = 39
time_of_day_value = PM

In the date column I want to insert the date "2007-01-12 01:17:39.000 PM"

Is this possible?

Thank you,
Kevin

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-10 : 16:30:58

the DateTime() function here should do the trick for you:

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx


update Yourtable
set YourTimeColumn = dbo.Datetime(century_value*100 + year_value, 
    month_value, 
    day_value, 
    hour_value + case when time_of_day_value='PM' then 12 else 0 end,
    minute_value,
    second_value)

you might have to double-check the hour formula, based on your data. For example, if it stores 0 as hour_value for midnight, that's fine, but if it stores 12, you would have to handle that and ensure that you return 0 for the hour.

- Jeff
http://weblogs.sqlteam.com/JeffS

raritan
Starting Member

39 Posts

Posted - 2007-07-10 : 17:39:42

i get the error "Invalid object name 'dbo.Datetime'." when trying to use this - any ideas? I am using SQL Query Analyzer to run the query.

Thanks
Kevin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-10 : 17:48:02

Did you create the function first from his link?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

raritan
Starting Member

39 Posts

Posted - 2007-07-10 : 17:59:16

oops no i did not :) I will add those functions and give it a go

Thank you
Kevin

raritan
Starting Member

39 Posts

Posted - 2007-07-10 : 18:08:46

ok I made a slight change to the update to account for 12 AM and 12 PM, versus any other AM or PM time:

update gc_usage
set call_date = dbo.Datetime(call_century_value*100 + call_year_value,
call_month_value,
call_day_value,
case
when call_hour_value = 12 then
call_hour_value + case when call_of_day_value='PM' then 0 else -12 end
else
call_hour_value + case when call_of_day_value='PM' then 12 else 0 end
end,
call_minute_value,
call_second_value)

other than that it worked perfectly - thanks so much for your help

Kevin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources