Date Conversion Problems - 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 2005 Forums

Transact-SQL (2005)

Date Conversion Problems

Author

Topic

walwinkle
Starting Member

6 Posts

Posted - 2009-01-20 : 05:27:40

Hi,
I have spent a lot of time on the web looking for ways around my problem. Any help would be appreciated.

I have a @recDate NVARCHAR(10) I pass a date (coming from a text field) into this variable. I cannot set the variable to a DATETIME because I don't know what format the date is going to be in and it can throw a conversion error. For Example: it could be be 2002-09-20 (20 SEPT 2002) OR 2002-09-02 (02 Sept 2002) OR 20/09/2002 (20 SEPT 2002) OR 02/09/2002 (02 Sept 2002).
In the end I want a dd/mm/yyyy format
I cannot use CONVERT() as it throws up an error depending on what format the date is in.
I cannot use DATENAME() to get the day, month, year as it throws up an error depending on what format the date is in.

Thanks

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-20 : 05:33:30

declare @date nvarchar(10)
select @date ='2002-09-20'

select convert(varchar(11),dateadd(d,0,@date),103) as 'dd/mm/yyyy'

Jai Krishna

walwinkle
Starting Member

6 Posts

Posted - 2009-01-20 : 05:41:12

Hi Jai,

That works great if the date is in '2002-09-20' format, but if it's in '20/09/2002' format you get an overflow error. Unfortunately I don't know what format the date will be in, all dates will either all be '2002-09-20' format or all '20/09/2002' format.
It's a nightmare.

Any ideas?
Maybe a try catch until it succeeds?

thanks

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-20 : 05:46:55

Try this

declare @date nvarchar(10)
select @date ='20/09/2002'

select case when charindex('-',@date,1) > 0 then convert(varchar(11),dateadd(d,0,@date),103) else @date end

Jai Krishna

walwinkle
Starting Member

6 Posts

Posted - 2009-01-20 : 05:51:06

Great, that seems to work for those date examples.

I am going to go through my scripts and update them and test it out on my data.

I will let you know,
thanks for that!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources