Date Conversion in sql 2005 - 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

General SQL Server Forums

New to SQL Server Programming

Date Conversion in sql 2005

Author

Topic

poratips
Posting Yak Master

105 Posts

Posted - 2010-03-31 : 14:52:00

Hi,
i have Date column stored in a Varchar datatype and in a different format.
Could you please guide that how can i update my date column and make it one format?


2008-09-01T09:00:01
2009-01-27T14:32:37.7389657-08:00
10/1/2009 2:49:13 PM

If I can update the column and keep one format like: 10/1/2009 2:49:13 PM or either one?
How can i do this?

Thanks for your help!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-31 : 15:30:49

Is "10/1/2009 2:49:13 PM" Oct 1 or Jan 10th? How can you tell?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 05:11:58

if your format is not consistent as Lamprey pointed it would be difficult to interpret whats the exact that it stores as in case above

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-01 : 05:19:41

This is why you should not use varchar datatype to store dates
Always use proper datetime datatype to store dates

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 05:48:32

Just to add to what the others have said. If you need to pass a date in text-string only use one of these formats (which are guaranteed to be parsed correctly, any other format is at the mercy of the server settings, country/language of the logged in user, and current value of dateformat

'yyyymmdd' - note NO hyphens
'yyyy-mm-ddThh:mm:ss.sss' - note that this DOES have hyphens

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 05:57:02

I ahven;t got a SQL 2005 server running, but is "2009-01-27T14:32:37.7389657-08:00" permitted [i.e. before SQL2008]?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 06:09:26

quote:
Originally posted by Kristen

I ahven;t got a SQL 2005 server running, but is "2009-01-27T14:32:37.7389657-08:00" permitted [i.e. before SQL2008]?

sorry but this didnt parse fine in my SQL 2008 also

-----------------------
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

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

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 06:16:40

[code]
SELECT CONVERT(datetime2, '2009-01-27T14:32:37.7389657-08:00')
[/code]
Seems OK here (in SQL2008) - but the -8 hours is ignored??
[code]
DECLARE @MyDateTime datetime,
@MyDateTime2 datetime2

SELECT @MyDateTime = CONVERT(datetime, '2009-01-27T14:32:37.738'), -- Rounds to .737
@MyDateTime2 = CONVERT(datetime2, '2009-01-27T14:32:37.7389657-08:00')

SELECT [@MyDateTime] = @MyDateTime,
[@MyDateTime2] = @MyDateTime2

SELECT [Match1] = CASE WHEN @MyDateTime = @MyDateTime2 THEN 'True' ELSE 'False' END,
[Match2] = CASE WHEN @MyDateTime = CONVERT(datetime2, '2009-01-27T14:32:37.7370000') THEN 'True' ELSE 'False' END

@MyDateTime @MyDateTime2
----------------------- ---------------------------
2009-01-27 14:32:37.737 2009-01-27 14:32:37.7389657

Match1 Match2
------ ------
False True
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-01 : 06:37:59

<<
'yyyy-mm-ddThh:mm:ss.sss' - note that this DOES have hyphens
>>

'yyyy-mm-ddThh:mm:ss.sss' - note that this DOES have hyphens and Time seperator T

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-01 : 06:59:46

quote:

Originally posted by Kristen


SELECT CONVERT(datetime2, '2009-01-27T14:32:37.7389657-08:00')

Seems OK here (in SQL2008) - but the -8 hours is ignored??


DECLARE	@MyDateTime	datetime,
	@MyDateTime2	datetime2

SELECT	@MyDateTime	= CONVERT(datetime, '2009-01-27T14:32:37.738'),	-- Rounds to .737
	@MyDateTime2	= CONVERT(datetime2, '2009-01-27T14:32:37.7389657-08:00')

SELECT	[@MyDateTime] = @MyDateTime,
	[@MyDateTime2] = @MyDateTime2

SELECT	[Match1] = CASE WHEN @MyDateTime = @MyDateTime2 THEN 'True' ELSE 'False' END,
	[Match2] = CASE WHEN @MyDateTime = CONVERT(datetime2, '2009-01-27T14:32:37.7370000') THEN 'True' ELSE 'False' END

@MyDateTime             @MyDateTime2
----------------------- ---------------------------
2009-01-27 14:32:37.737 2009-01-27 14:32:37.7389657

Match1 Match2 
------ ------ 
False  True

In versions prior to 2008, millisecond values are rounded to 0.000, 0.003 and 0.007. Note that last digit is always 0,3 or 7
So values like 735,736,737 and 738 are rounded to 737

Version 2008 doesn't do rounding on millisecond part. It allows 7 digits for milliscond part

Also in this datetime '2009-01-27T14:32:37.7389657-08:00', -08:00 will be omitted as it is not as part of datetime or datetime2 but will be cosidered for datetimeoffset

select cast('2009-01-27T14:32:37.7389657-08:00' as datetimeoffset)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 07:04:54

Yup, good point Madhi. Pity there is so much "special knowledge" required for DATETIME because of the plethora of formats that SQL will attempt to parse - usually with potential side effects and often with unexpected consequences

poratips
Posting Yak Master

105 Posts

Posted - 2010-04-01 : 07:32:23

Thanks Kristen and Madhivanan for your response, really appreciated.
I am running on Sql server 2005 so can't test DateTime2 or datetimeoffset.
you are absolutely right that we made a mistake to use varchar instead of Datetime.
10/1/2009 2:49:13 PM, it will be MM/DD/YYYY format.
If you show me the way to update my all the records in a one foramt like:10/1/2009 2:49:13 PM/AM, really appreciated.

Thanks,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-01 : 08:54:49

select cast(left(dates,23) as datetime) from
(
select '2008-09-01T09:00:01' as dates union all
select '2009-01-27T14:32:37.7389657-08:00' union all
select '10/1/2009 2:49:13 PM'
) as t

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 09:01:33

Quite. Do

SET LANGUAGE British

then check the value of the third one. Then try

SET LANGUAGE English

and recheck the third one

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-01 : 09:16:39

quote:
Originally posted by Kristen

Quite. Do

SET LANGUAGE British

then check the value of the third one. Then try

SET LANGUAGE English

and recheck the third one

I assume it be default Language English

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 09:27:27

SET LANGUAGE Indian

is not supported I'm afraid

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-01 : 09:34:21

quote:
Originally posted by Kristen

SET LANGUAGE Indian

is not supported I'm afraid

Yes. The reason is

Msg 2740, Level 16, State 1, Line 2
SET LANGUAGE failed because 'Indian' is not an official language name or a language alias on this SQL Server.

Thats why I use dateformat

set dateformat mdy

select cast(left(dates,23) as datetime) from
(
select '2008-09-01T09:00:01' as dates union all
select '2009-01-27T14:32:37.7389657-08:00' union all
select '10/1/2009 2:49:13 PM'
) as t
Declare @myDate datetime

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources