convert one date format to another? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2005)

convert one date format to another?

Author

Topic

Matt2k9
Starting Member

10 Posts

Posted - 2008-09-12 : 09:10:08

Hi, how can I convert one date format to another? I have a procedure (below) where 2 dates are passed in (in UK format). Ideally I would like to convert these into IS0 112 format before the query uses them.
As the query stands below, I get an 'date was out of range, please pull your hair out in chunks now' exception - because I am trying to enter a UK date. However if I was able to somehow convert dateparam and endateparam into ISO 112 format like 20080910 and 20080917, the query would run ok. Any ideas of how I can do this??

I do not want to use set dateformat in the query as this causes query recompilation and thus further chunks of hair to be lost further down the line!

Hope someone can help! Thanks,
Matt

declare @dateparam datetime
set @dateparam = '10/09/2008'

declare @enddateparam datetime
set @enddateparam = '17/09/2008'

declare @converteddate datetime
set @converteddate = convert(nvarchar(100), @dateparam, 112)

declare @endconverteddate datetime
set @endconverteddate = convert(nvarchar(100), @enddateparam, 112)

select * from test where date between @converteddate and @endconverteddate

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-12 : 09:37:11

You can never distinguish between uk format '01/11/2008' and us format '01/11/2008'.
If either day part is greater than 12 you can "see" which format it is, but not else.

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-12 : 09:42:45

Are you using an application that sends dates to the Server?
Try using datetime parameter there and if you accept uk format from users, convert it to YYYMMDD format and send it to server

Madhivanan

Failing to plan is Planning to fail

Matt2k9
Starting Member

10 Posts

Posted - 2008-09-15 : 04:23:44

After pulling my hair out for ages in sql server management studio, I finally did a test with my application. It turns out that asp.net applications automatically convert dates into yyyymmdd format. Woohoo!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources