Date Formats - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Date Formats

Author

Topic

Princess21
Starting Member

1 Post

Posted - 2011-11-21 : 15:08:19

Hi wondered if any one could help. I have the following query that works fine -

Declare @dateworking datetime
declare @endweekdate datetime
Declare @bh as int

Set @dateworking = dateadd(wk,4,{creation_date})
If DatePart(dw,@dateworking) = 1
Set @dateworking = dateadd(dd,1,@dateworking)
If DatePart(dw,@dateworking) = 3
Set @dateworking = dateadd(dd,6,@dateworking)
If DatePart(dw,@dateworking) = 4
Set @dateworking = dateadd(dd,5,@dateworking)
If DatePart(dw,@dateworking) = 5
Set @dateworking = dateadd(dd,4,@dateworking)
If DatePart(dw,@dateworking) = 6
Set @dateworking = dateadd(dd,3,@dateworking)
If DatePart(dw,@dateworking) = 7
Set @dateworking = dateadd(dd,2,@dateworking)

set @endweekdate = dateadd(dd,3,@dateworking)

set @bh = (select COUNT([holiday])
FROM [harlowfoi_data].[dbo].[public_holiday]
where holiday between @dateworking and @endweekdate)

If cast(@dateworking as char(10)) = '26-12-11'
set @dateworking = dateadd(dd,8,@dateworking)
Else
set @dateworking = dateadd(dd,@bh,@dateworking)

select @dateworking as tenancy_completion

However this part of the query does not work, I have been told to change the date format and have tried this and nothing seems to pick this part of the query up.

If cast(@dateworking as char(10)) = '26-12-11'
set @dateworking = dateadd(dd,8,@dateworking)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-11-21 : 17:32:12

Instead of using CAST to change the data type, you could use CONVERT. The advantage is that CONVERT allows you to specify the format of the string produced. In your instance, using CONVERT(char(10), @dateworking, 5) will produce the desired results.

Having said that, if the @dateworking variable does not have a time component value, you could juat change the date string literal from '26-12-11' to be '20111226'. The system will always accept dates in the format of 'YYYYMMDD' regardless of the current date format.

If the @dateworking variable does not have a time component value, why not use DATE instead of DATETIME?

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 01:42:22

Just to reiterate, its always better to specify date values in unambiguos iso format like YYYYMMDD

the value 26-12-11 can be interpreted as 26 Dec 2011 or 11 Dec 2026 depending on date format as well as regional setting of sql server

see below example



set language Latvian
declare @d datetime
set @d='26-12-11'
select @d
go

set language [British English]
declare @d datetime

set @d='26-12-11'
select @d
go

output
----------------------------
Changed language setting to latviešu.

-----------------------
2026-12-11 00:00:00.000

(1 row(s) affected)

Changed language setting to British.

-----------------------
2011-12-26 00:00:00.000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-22 : 01:51:39

Also make sure to read this post
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources