October 14, 2009 at 12:36 pm
Hi Everyone!
I have a question about (the dreaded) DateTime conversion. Im a SQL report writer for 3 years now and its still the thing that always gets me!!!
Have a look at the following Code
SELECT CONVERT(VARCHAR, dtTimestamp, 105) AS Date
FROM dbo.TransactionHistory
WHERE (CAST(CONVERT(varchar, dtTimestamp, 105) AS datetime) = CAST('20090101' AS datetime))
I just wrote this code as a sample to show what a script i have (a script that is waaaay too large to put on here) is doing.
The error im getting is "The conversion of a char data type resulted in an out of range datetime value"
Anyone anyidea why im getting this?? I always use the YYYYMMDD format and that usually works fine. I'm stumped
Cheers
Dee
October 14, 2009 at 12:52 pm
You are getting the conversion error because DD-MM-YYYY is not the current dateformat on your connection.
Your conversion of the dtTimestamp column to a string and then back to a datetime is not a good way to write the query because it prevents the query from using an index on the column.
Date range queries should be in the form of DateCol >= startdatetkime and DateCol < enddatetime to allow them to use an index.
select
convert(varchar,dtTimestamp, 105) as Date
from
dbo.TransactionHistory
where
dtTimestamp >= '20090101' and
dtTimestamp < '20090102'
October 14, 2009 at 1:10 pm
Hiya
Im aware that its not the best way to do things but my problem lies in my script...
It calls a view I created. The view pulls the information from the transactiontable and groups it. as the Timestamp was one field and the timestamp was stamped in date time minutes seconds, it was skewing my Group By so I converted it to Date Format 105. Now i want to be able to search within a date range on the view and as a result the Date is spitting back an error at me.
For Example
Code For My View (lets call is vw_transaction)
SELECT CONVERT(VARCHAR, dtTimeStamp,105) AS Date, sCustomerID as CustomerNumber, SUM(mDebit) as Debit FROM TransactionHistory WHERE TransactionType LIKE '%CHARGE%' GROUP BY Date, CustomerNumber
Then Im pulling info from that view and I am saying
SELECT Date, CustomerNumber, Debit WHERE CAST(Date as DateTime) = CAST('20090101' as DateTime)
Hence, my error......
October 14, 2009 at 2:42 pm
Anyone any ideas???
Dee
October 14, 2009 at 4:32 pm
If your [dtTimeStamp] column is indexed, one suggestion would be to retain the [dtTimeStamp] in its original datetime format, and add another column ([GroupByDate]) in your view.
The [GroupByDate] column would use the CONVERT() function to truncate time and turn dtTimeStamp into whatever format you want to display.
You could group on the [GroupByDate] column, but you would still be able to query directly against the original dtTimeStamp column to take advantage of the index. If there is no index on dtTimeStamp, it's really a moot point as far as performance goes, although you're wasting cpu cycles doing so many conversions.
Personally, I just work with the base datetime and do conversions as necessary.
select convert(varchar(8),dtTimeStamp,112) as dtTimeStamp, etc, etc.
from someTable
where dtTimeStamp >= @someDate and dtTimeStamp < @someOtherDate
group by convert(varchar(8),dtTimeStamp,112)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 14, 2009 at 4:50 pm
Michael Valentine Jones has the correct solution so I'd suggest you change your view accordingly. But, since you already have the view you can change your select from the view like:
SELECT Date, CustomerNumber, Debit
FROM vw_transaction
WHERE Date = '14-10-2009' -- 14 Oct 2009
--OR
SELECT Date, CustomerNumber, Debit
FROM vw_transaction
WHERE Date = CONVERT(VARCHAR, CAST('20090101' as DateTime), 105)
October 14, 2009 at 5:23 pm
Thank you so much everyone for all your help!!! You saved me big time 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply