[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Inconsistency with combining date and type datatypes to datetime

Posted on 2012-09-05
4
Medium Priority
?
414 Views
Last Modified: 2012-09-25
Hello everyone,

I'm having a strange problem with combining date and time values to datetime.  What is happening is that I have a fact table which I join to a date dimension and a time dimension based on their respective keys, then in my where predicate I define what datetime value I'm looking for.  For example:
select fact.*
from myfact fact
join
date_dim dt 
on fact.date_key = dt.date_key
join
time_dim tm
on fact.time_key = tm.time_key
where
(CAST(dt.date as datetime) + CAST(tm.time as datetime)) = getdate()

Open in new window


Most of the time this works.  But once in a while I get an "out of range" error when it converts varchar to datetime (the tm.time column is actually char(8) to begin with).  The only way I could make that problem go away is by changing the cast logic to this:
select fact.*
from myfact fact
join
date_dim dt 
on fact.date_key = dt.date_key
join
time_dim tm
on fact.time_key = tm.time_key
where
CAST(CAST(dt.date as nvarchar(10)) + ' ' + CAST(tm.time as nvarchar(8)) as datetime) = getdate()

Open in new window



Can anyone explain or maybe have any ideas why the first method behaves inconsistently?

Thanks,
Glen
0
Comment
Question by:jisoo411
  • 2
4 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 38370558
If you're using SQL Server 2008 or later, have you thought of changing your system to use the date and time types? At least, in this way, you would find any problems "up front" rather than discovering them at runtime.

Aside from that, can you show some examples from your data where (CAST(dt.date as datetime) + CAST(tm.time as datetime)) doesn't make a datetime ?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38372526
jisoo411,

Beyond your immediate problems with casting other values into datetimes, I must warn you that using:

WHERE <some datetime value> = GETDATE()

is an incredibly bad idea.  GETDATE() returns the current system date AND TIME down to a granularity of about 3 miliseconds.  As such, the likelihood of getting an exact match down to that level is exceedingly small.

You are probably better off specifying a range.  For example, to get values within one minute of the current system date and time:

WHERE <some datetime value> BETWEEN DATEADD(minute, -1, GETDATE()) AND DATEADD(minute, 1, GETDATE())

Patrick
0
 

Author Comment

by:jisoo411
ID: 38374380
Using GETDATE() was just a quick and dirty example, in reality I do use a BETWEEN specification in the where predicate.  

The main problem is the datatype conversion, I haven't been able to nail down an example because most of the time it works.  When I do get the error message, the data that is used to build the new datetime value looks normal.  These values are built off common values from date and time dimension tables so they're used constantly.  It's just really odd that sometimes this problem arises and I end up having to do the datatype conversion in a different way to achieve the same (previously working) result....

*Note*: the source column from the date dimension table is of "date" type and the source column from the time dimension is "char(8)", although that shouldn't be a problem (and usually isn't).

Thanks,
Glen
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 38374644
To find the time values that will not convert, use:

SELECT *
FROM time_dim
WHERE ISDATE([time]) = 0

Open in new window


You'll want to clean those up.

In the meantime, to run your query excluding items where the time column cannot convert:

select fact.*
from myfact fact join
    date_dim dt on fact.date_key = dt.date_key join
    time_dim tm on fact.time_key = tm.time_key
where ISDATE(tm.time) = 1 AND
    (CAST(dt.date as datetime) + tm.time) = getdate()

Open in new window


To force a time portion of midnight where that time column cannot convert:

select fact.*
from myfact fact join
    date_dim dt on fact.date_key = dt.date_key join
    time_dim tm on fact.time_key = tm.time_key
where (CAST(dt.date as datetime) + CASE WHEN ISDATE(tm.time) = 1 THEN tm.time ELSE '00:00:00' END) = getdate()

Open in new window

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question