| Author |
Topic |
|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2008-12-10 : 18:33:43
|
| As a greenbean sql guy...the concept of timestamps based on an integer field that counts from 1/1/1970 (?) escapes me...this code should work, I think...help?I get this error: Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type datetime. If I remove the WHERE, it works...so there is something I'm missing related to the compare of the interger field 'Create_time' and the ts function???in one record, for example, create_time = 1184014446SELECT Actual_End_Date, Actual_Start_Date, Auto_close_Days, Auto_close_Time, Auto_End_Time, Auto_Start_Time, Category, Create_Time, Description, Estimated_Total_Time, Hours_to_resolve, IncidentID, HPD_HelpDesk.instanceId, Modified_Time, Resolved_Time, Solution_Description, Solution_Summary, Status , Time_Spent__min_, Total_Time_SpentFROM HPD_HelpDesk WHERE (Create_Time>={ts '2008-09-01 00:00:00'}) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2008-12-11 : 09:44:06
|
| mmm..I use sql mgmt studio, have read-only access to the database...how might I utilize a udf? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-11 : 10:05:32
|
just run this and see if you've some invalid datesselect * from HPD_HelpDesk WHERE ISDATE(Create_Time)=0OR (ISDATE(Create_Time)=1 AND LEN(Create_Time)<8) and correct the invalid values |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2008-12-11 : 10:13:38
|
| no invalid dates...but I did some surfing..and found a note on conversion using dateadd...dateadd(s,Create_Time,'19700101')as Createdateconverts my integer in create_time, to a date...so my where looks like:WHERE (dateadd(s,Create_Time,'19700101')>={ts '2008-09-01 00:00:00'})and(dateadd(s,Create_Time,'19700101')<{ts '2008-10-01 00:00:00'})to pull records for the month of september...does this make sense? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-11 : 10:28:42
|
| shouldnty it be dateadd(s,Create_Time,'19000101') rather than dateadd(s,Create_Time,'19700101')? |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2008-12-11 : 11:16:22
|
| maybe it depends on the database architecture to pick that magic date..but that particular conversion, works with my database...my dates seem to convert into reasonable date for my helpdesk application (ie. I do get 9/1/2008-9/30/2008 records...if I changed to 1/1/1900, I expect I would get some weird dates..mm?...and I do..the dates convert to 1937... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
time stamp?