February 17, 2013 at 1:26 am
Okay so this is a very odd situation, one that I have not seen before.
I'm trying to query a table which stores dates in the following format - '2013-02-13 00:00:00.000'. When I try to query the table between two dates - 2012-08-01 00:00:00.000 and 2013-02-13 00:00:00.000 it only returns a certain number of rows which doesn't look right. In other words, it's only returning one store location for the entire date range. Sure enough, when I query the entire table I get the full list of store locations returning between those two dates. I don't get it - what causes this?
The reason I ask is because I don't have a way to test my logic in DEV. The production environment was changed this weekend and i'm updating the stored procedures, however the DEV environment was not changed for whatever reason. I don't have access to production. I have to create my own test tables based on the dates. I haven't used SQL Server 2005 in a long time so finding the date issue perplexing (normally, i'd just cast to date type).
Thanks!!
February 17, 2013 at 2:20 am
Sorry, I managed to figure out what was going on. It actually had nothing to do with the dates.
Ugh, i'm under a little bit of pressure this weekend which is causing me stress. I wasn't given all of the info regarding the client environment and not having a way to test my changes is driving me crazy. Unfortunately I don't have a way to contact them over the weekend. This will be the LAST TIME that I take a short assignment without speaking to the client first. Man, things are really messed-up there, LOL.
February 18, 2013 at 9:03 am
SQL Server doesn't store date/times values in a format, it's just a typed integer.
However, SET DATEFORMAT setting influences how date/time values are displayed in a resultset and how strings are converted to date/time values. Also, when using the DateTimeOffset datatype, time zone offset comes into play.
When comparing a date/time value to a range, I'd reccomend doing it like this rather than doing a BETWEEN:
WHERE DateColumn >= '2012-08-01' AND DateColumn < '2013-02-14'
This insures that you're looking at only date and the time portion is ingored. Also, the string format YYYY-MM-DD is ISO standard and always translates properly regardless of local language or DATEFORMAT setting.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 18, 2013 at 9:12 am
Eric M Russell (2/18/2013)
SQL Server doesn't store date/times values in a format, it's just a typed integer.However, SET DATEFORMAT setting influences how date/time values are displayed in a resultset and how strings are converted to date/time values. Also, when using the DateTimeOffset datatype, time zone offset comes into play.
When comparing a date/time value to a range, I'd reccomend doing it like this rather than doing a BETWEEN:
WHERE DateColumn >= '2012-08-01' AND DateColumn < '2013-02-14'
This insures that you're looking at only date and the time portion is ingored. Also, the string format YYYY-MM-DD is ISO standard and always translates properly regardless of local language or DATEFORMAT setting.
Not true:
Also, the string format YYYY-MM-DD is ISO standard and always translates properly regardless of local language or DATEFORMAT setting.
The 'YYYY-MM-DD' string format is affected by the setting of DATEFORMAT. Using 'YYYYMMDD' is not affected by the setting of DATEFORMAT.
February 18, 2013 at 9:42 am
Lynn Pettis (2/18/2013)
Eric M Russell (2/18/2013)
SQL Server doesn't store date/times values in a format, it's just a typed integer.However, SET DATEFORMAT setting influences how date/time values are displayed in a resultset and how strings are converted to date/time values. Also, when using the DateTimeOffset datatype, time zone offset comes into play.
When comparing a date/time value to a range, I'd reccomend doing it like this rather than doing a BETWEEN:
WHERE DateColumn >= '2012-08-01' AND DateColumn < '2013-02-14'
This insures that you're looking at only date and the time portion is ingored. Also, the string format YYYY-MM-DD is ISO standard and always translates properly regardless of local language or DATEFORMAT setting.
Not true:
Also, the string format YYYY-MM-DD is ISO standard and always translates properly regardless of local language or DATEFORMAT setting.
The 'YYYY-MM-DD' string format is affected by the setting of DATEFORMAT. Using 'YYYYMMDD' is not affected by the setting of DATEFORMAT.
I can see where YYYY-MM-DD is affacted by SET LANGUAGE, but I'm not seeing it affacted by SET DATEFORMAT. You're right that YYYYMMDD would be the best for all locations and environments.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 18, 2013 at 9:46 am
Eric M Russell (2/18/2013)
Lynn Pettis (2/18/2013)
Eric M Russell (2/18/2013)
SQL Server doesn't store date/times values in a format, it's just a typed integer.However, SET DATEFORMAT setting influences how date/time values are displayed in a resultset and how strings are converted to date/time values. Also, when using the DateTimeOffset datatype, time zone offset comes into play.
When comparing a date/time value to a range, I'd reccomend doing it like this rather than doing a BETWEEN:
WHERE DateColumn >= '2012-08-01' AND DateColumn < '2013-02-14'
This insures that you're looking at only date and the time portion is ingored. Also, the string format YYYY-MM-DD is ISO standard and always translates properly regardless of local language or DATEFORMAT setting.
Not true:
Also, the string format YYYY-MM-DD is ISO standard and always translates properly regardless of local language or DATEFORMAT setting.
The 'YYYY-MM-DD' string format is affected by the setting of DATEFORMAT. Using 'YYYYMMDD' is not affected by the setting of DATEFORMAT.
I can see where YYYY-MM-DD is affacted by SET LANGUAGE, but I'm not seeing it affacted by SET DATEFORMAT. You're right that YYYYMMDD would be the best for all locations and environments.
Run this:
declare @DateVal datetime;
set dateformat mdy;
set @DateVal = '2013-02-14';
select @DateVal;
go
declare @DateVal datetime;
set dateformat dmy;
set @DateVal = '2013-02-14';
select @DateVal;
go
February 18, 2013 at 4:54 pm
Lynn Pettis (2/18/2013)
Eric M Russell (2/18/2013)
Lynn Pettis (2/18/2013)
Eric M Russell (2/18/2013)
SQL Server doesn't store date/times values in a format, it's just a typed integer.However, SET DATEFORMAT setting influences how date/time values are displayed in a resultset and how strings are converted to date/time values. Also, when using the DateTimeOffset datatype, time zone offset comes into play.
When comparing a date/time value to a range, I'd reccomend doing it like this rather than doing a BETWEEN:
WHERE DateColumn >= '2012-08-01' AND DateColumn < '2013-02-14'
This insures that you're looking at only date and the time portion is ingored. Also, the string format YYYY-MM-DD is ISO standard and always translates properly regardless of local language or DATEFORMAT setting.
Not true:
Also, the string format YYYY-MM-DD is ISO standard and always translates properly regardless of local language or DATEFORMAT setting.
The 'YYYY-MM-DD' string format is affected by the setting of DATEFORMAT. Using 'YYYYMMDD' is not affected by the setting of DATEFORMAT.
I can see where YYYY-MM-DD is affacted by SET LANGUAGE, but I'm not seeing it affacted by SET DATEFORMAT. You're right that YYYYMMDD would be the best for all locations and environments.
Run this:
declare @DateVal datetime;
set dateformat mdy;
set @DateVal = '2013-02-14';
select @DateVal;
go
declare @DateVal datetime;
set dateformat dmy;
set @DateVal = '2013-02-14';
select @DateVal;
go
OH! Now THAT's interesting! I never even considered that particular format might be affected by DateFormat. Thanks, Lynn. The only place I've ever used the dashed format is for human readability on these very forums. It's amazing I've not run into it before, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 18, 2013 at 5:30 pm
Bit more to think about:
-- datetime data type
declare @DateVal datetime;
set dateformat mdy;
set @DateVal = '2013-02-14';
select @DateVal;
go
declare @DateVal datetime;
set dateformat dmy;
set @DateVal = '2013-02-14';
select @DateVal;
go
declare @DateVal datetime;
set dateformat mdy;
set @DateVal = '2013-14-02';
select @DateVal;
go
declare @DateVal datetime;
set dateformat dmy;
set @DateVal = '2013-14-02';
select @DateVal;
go
-- SQL Server 2008 and newer, Date and Datetime2 data types
declare @DateVal date;
set dateformat mdy;
set @DateVal = '2013-02-14';
select @DateVal;
go
declare @DateVal datetime2;
set dateformat dmy;
set @DateVal = '2013-02-14';
select @DateVal;
go
declare @DateVal datetime2;
set dateformat mdy;
set @DateVal = '2013-02-14';
select @DateVal;
go
declare @DateVal datetime2;
set dateformat dmy;
set @DateVal = '2013-02-14';
select @DateVal;
go
declare @DateVal date;
set dateformat mdy;
set @DateVal = '2013-14-02';
select @DateVal;
go
declare @DateVal datetime2;
set dateformat dmy;
set @DateVal = '2013-14-02';
select @DateVal;
go
declare @DateVal datetime2;
set dateformat mdy;
set @DateVal = '2013-14-02';
select @DateVal;
go
declare @DateVal datetime2;
set dateformat dmy;
set @DateVal = '2013-14-02';
select @DateVal;
go
February 18, 2013 at 5:43 pm
LOL, glad I could help facilitate some education on date formats and querying. I think this is one of the more confusing areas of querying data.
I'm also mostly an SSRS developer. I do put queries together for use in reports (obviously), but I also lack when it comes to solid DBA skills and foundation. I've taken quite a few courses regarding business intelligence at a local community college and this has helped keep me employed, but one thing i've noticed is that most college courses don't spend a lot of time on the DBA side of development which I think is wrong. It's valuable to know about execution plans and how to optimize queries, that should be a solid foundation. However, I've had to learn most of that stuff on-the-fly and on the job. I also don't know a lot of what I probably should know regarding dates.
So, i'm seeking a course or courses in database administration.
Thanks All, for responding on my thread. You're all a great help.
February 19, 2013 at 5:13 am
And here's a really obvious (yeah right!) way of making Lynn's example work:
declare @DateVal datetime;
set dateformat mdy;
set @DateVal = '2013-02-14T00:00:00';
select @DateVal;
go
declare @DateVal datetime;
set dateformat dmy;
set @DateVal = '2013-02-14T00:00:00';
select @DateVal;
go
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 19, 2013 at 8:39 am
Below I've setup a practical illustration of why this matters. Using the exact same SQL query, the sum of payments is 100.00 when LANGUAGE is set to US_ENGLISH, but the sum is 300.00 for ITALIAN and FRENCH.
Thanks, Lynn, for pointing out that YYYYMMDD (not YYYY-MM-DD) is the correct format to use for LANGUAGE and DATEFORMAT setting independence. We have to remember that the application or client tool can apply these settings at the connection level, so it would be best to always use YYYYMMDD format in our SQL queries.
create table #customer_payment
( primary key( customer_id, payment_date),
customer_id int not null, payment_date datetime not null,
payment_amt smallmoney not null );
insert into #customer_payment
( customer_id, payment_date, payment_amt )
values
( 123, '20120112', 100.00 ),
( 123, '20120505', 100.00 ),
( 123, '20121207', 100.00 );
Then, run the following query under each language:
select sum(payment_amt)payment_amt
from #customer_payment
where customer_id = 123 and payment_date >= '2012-10-01';
SET LANGUAGE US_ENGLISH;
Changed language setting to us_english.
payment_amt
---------------------
100.00
SET LANGUAGE ITALIAN;
L'impostazione della lingua è stata sostituita con Italiano.
payment_amt
---------------------
300.00
Le paramètre de langue est passé à Français.
SET LANGUAGE FRENCH;
payment_amt
---------------------
300.00
However, modifying the search condition to YYYYMMDD format like below, the query returns 100.00 under all three languages.
select sum(payment_amt)payment_amt
from #customer_payment
where customer_id = 123 and payment_date >= '20121001';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply