Date Format

Question

Date Format

Kevin-130791

SSChasing Mays

Points: 622
More actions
February 28, 2005 at 10:28 am

#87732

Can data in this format DDMMYYYY be stored in a datetime column? I have created a temp table with the column define is a datatime, but when I inserted the value it failed because it was out of range? Do I have to change the data type to nchar/varchar?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Will your data always be in that format? If so, you have a number of options. Because I am American, I would prefer the following: 13071961 would be the value.

SELECT SUBSTRING( '13071961', 3, 2) + '/' + SUBSTRING( '13071961', 1, 2) + '/' + SUBSTRING( '13071961', 5, 4)

I wasn't born stupid - I had to study.

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

MY GOOF! I had the wrong numbers in the SUBSTRING.

This will work:

SELECT CONVERT( datetime, SUBSTRING( '13071961', 3, 2) + '/' + SUBSTRING( '13071961', 1, 2) + '/' + SUBSTRING( '13071961', 5, 4))

FORGET THIS KNUCKLEHEADED APPROACH....

I played with this a bit more and found an odd outcome that I do not understand.

SELECT CONVERT( datetime, SUBSTRING( '13071961', 2, 2) + '/' + SUBSTRING( '13071961', 1, 2) + '/' + SUBSTRING( '13071961', 5, 4))

Results in the following error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(If that is out of range, I am too old to use SQL Server... )

p.s. I tried converting it to varchar as well and smalldatetime does not work either...

I wasn't born stupid - I had to study.

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Hi Farrell,

the error is caused by SUBSTRING( '13071961', 2, 2) , which should be SUBSTRING( '13071961', 3, 2) . Just a typo, I guess, because you have it right in the previous example.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

See if this provides additional help:

http://www.karaszi.com/sqlserver/info_datetime.asp

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

DLathrop SSCrazy Points: 2018 More actions · Answer 5

FYI. This won't help much for dates in "mmddyyyy" format, but for dates in "yyyymmdd" format its faster and easier to use the CONVERT function. This has an optional third "style" argument specifically for handling different formats. See SQL Books Online.

convert( datetime, '20050228', 112)

This would also allow you to simply rearrange the digits with SUBSTRING and + but not bother with the two '/' literals.

If your code might have to work with different language settings, then it should probably also use CONVERT with a style so you don't run into problems with USA 02/28/2005 not being recognized as 28.02.2005 under British/French setting.

David Lathrop
DBA
WA Dept of Health

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 6

Vasc

SSCarpal Tunnel

Points: 4643

March 1, 2005 at 1:33 pm

#544691

See

SET DATEFORMAT dmy

Kindest Regards,

Vasc

Erik Schaible SSC Rookie Points: 28 More actions · Answer 7

This may just be academic, but..

Remember that when you talk about the "format" of a date/time value, you're strictly talking about presentation. No matter how the date/time is represented, the actual datetime datatype is stored as two numbers that specify the number of days since Jan 1, 1900 and the number of milliseconds since midnight.

As has already been posted, you can explicitly define which format to use in a CONVERT function, or use SET DATEFORMAT to set the default.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

the actual datetime datatype is stored as two numbers that specify the number of days since Jan 1, 1900 and the number of milliseconds since midnight

That's incorrect information from BOL.

Now, the second 4 bytes of a DATETIME do store the time. Try and enter a value of 300 into this part and you'll get 1 second as a result.

Consider this:

DECLARE @300 BINARY(8)

SET @300 = 0x00000000 + CAST(300 AS BINARY(4))

SELECT @300, CAST(@300 AS DATETIME)

------------------ ------------------------------------------------------

0x000000000000012C 1900-01-01 00:00:01.000

(1 row(s) affected)

So, when the integer value of 300 results in 1 second, one can come to the reverse conclusion that *not* 300 milliseconds are stored, but rather 300/300 of a second, that is 1 second.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

philcart SSC-Forever Points: 47794 More actions · Answer 9

Maybe thats wher the accuracy comes into play,

From BOL,

Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.

--------------------
Colt 45 - the original point and click interface

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

It depends. Stored in a DATETIME are the clock ticks since midnight. Every second consist of 300 ticks. They are more precise than @@TIMETICKS.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]