formatting for a birth date field??

**David Portas** · Sep 16 '06, 01:15 PM

Re: formatting for a birth date field??

tlyczko wrote:

Hello, I'm new to SQL Server, working for a non-profit computerizing a
lot of its data.
>
I imported a table of people's names, birth dates, etc. into SS2005
from Access, and the birth_date was imported as an Access date/time
field, giving it the datetime datatype in SQL.
>
The column values look like:
>
10/14/1964 12:00:00 AM
>
Where and how do I learn to specify that all fields like this should be
in ISO format of yyyy-mm-dd??
>
Do I have to create a new column and put all the dates into it??
Should I just convert the data in queries/views??
Use a constraint to format the data??
I can redo the Access table if necessary, it is only 300-some rows.
>
I tried BOL but it was not helpful...
>
The end users will likely enter mm/dd/yy or mm/dd/yyyy and it will have
to be stored properly in the database table as column/field
birth_date...
>
Thank you, Tom

A DATETIME column doesn't have any specific format. SQL Server cannot
control the format of dates as displayed by your client application.
For that you have to use the features of your client app or development
environment.Typ ically these might be based on the regional format
defined in Windows Control Panel.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

Books Online

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

**Hugo Kornelis** · Sep 17 '06, 07:55 PM

Re: formatting for a birth date field??

On 16 Sep 2006 06:12:07 -0700, tlyczko wrote:

>Hello, I'm new to SQL Server, working for a non-profit computerizing a
>lot of its data.

(snip)

>Where and how do I learn to specify that all fields like this should be
>in ISO format of yyyy-mm-dd??

(snip)

>The end users will likely enter mm/dd/yy or mm/dd/yyyy and it will have
>to be stored properly in the database table as column/field
>birth_date.. .

Hi Tom,

Read Tibor Karaszi's article "The ultimate guide to the datetime
datatypes", and you'll know everything you need to know for safely using
datetimes in SQL Server databases:

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

--
Hugo Kornelis, SQL Server MVP

**tlyczko** · Sep 18 '06, 12:45 PM

Re: formatting for a birth date field??

David Portas wrote:

A DATETIME column doesn't have any specific format. SQL Server cannot
control the format of dates as displayed by your client application.
For that you have to use the features of your client app or development
environment.Typ ically these might be based on the regional format
defined in Windows Control Panel.
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

I was looking at the table itself through SSMS 2005, I did specify
above which server I was working in, there were no error messages, it
was an import using the wizard, but I'll remember the create/insert
etc. items next time.

Thank you, Tom

**tlyczko** · Sep 18 '06, 01:15 PM

Re: formatting for a birth date field??

Hugo Kornelis wrote:

Read Tibor Karaszi's article "The ultimate guide to the datetime
datatypes", and you'll know everything you need to know for safely using
datetimes in SQL Server databases:
http://www.karaszi.com/SQLServer/info_datetime.asp

Thank you, I shall...I was looking at the SQL Server table itself in
SSMS 2005, and the above post mentions how SQL stores/displays data,
maybe that's where I'm getting confused, smalldatetime will work for
me, I am only concerned with dates and *maybe* time to the nearest
minute.

Thank you, Tom

**Erland Sommarskog** · Sep 18 '06, 10:05 PM

Re: formatting for a birth date field??

tlyczko (tlyczko@gmail. com) writes:

I was looking at the table itself through SSMS 2005,

I don't think so. I think you looked at a textual representation of the
table, as presented by SSMS.

I believe that when you run a SELECT query, you always get ISO format,
but in Open Table regional settings are applied. I cannot really tell
for sure, since my regional settings agree with the ISO format.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**tlyczko** · Sep 19 '06, 12:15 PM

Re: formatting for a birth date field??

Erland Sommarskog wrote:

tlyczko (tlyczko@gmail. com) writes:

I was looking at the table itself through SSMS 2005,

>
I don't think so. I think you looked at a textual representation of the
table, as presented by SSMS.
I believe that when you run a SELECT query, you always get ISO format,
but in Open Table regional settings are applied. I cannot really tell
for sure, since my regional settings agree with the ISO format.

Hello, both ideas make sense, however, I now just need to learn how to
update all the fields such that the TIME part for each datum is
00:00:00.etc., I don't need to worry about the time in a birth date
field or anything similar...I'll do this as a separate post.

Thank you, Tom

**Erland Sommarskog** · Sep 19 '06, 08:55 PM

Re: formatting for a birth date field??

tlyczko (tlyczko@gmail. com) writes:

Hello, both ideas make sense, however, I now just need to learn how to
update all the fields such that the TIME part for each datum is
00:00:00.etc., I don't need to worry about the time in a birth date
field or anything similar...I'll do this as a separate post.

CONSTRAINT dateonly CHECK (convert(char(8 ), birthdate, 112) = birthdate)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**tlyczko** · Sep 19 '06, 09:55 PM

Re: formatting for a birth date field??

Erland Sommarskog wrote:

tlyczko (tlyczko@gmail. com) writes:

Hello, both ideas make sense, however, I now just need to learn how to
update all the fields such that the TIME part for each datum is
00:00:00.etc., I don't need to worry about the time in a birth date
field or anything similar...I'll do this as a separate post.

>
CONSTRAINT dateonly CHECK (convert(char(8 ), birthdate, 112) = birthdate)

Hello Erland,
Thank you for taking time to reply...you'll get a lot of stars from
me!! :) :)
Now I know what to read about to begin understanding your statement
above, I'll also add that constraint and check to the field itself (and
other fields too).
Thank you, Tom

**Alexander Kuznetsov** · Sep 19 '06, 11:05 PM

Re: formatting for a birth date field??

Hi Erland,

I did a quick benchmarking. I always knew that DATEDIFF approach should
be faster than converting to CHAR, but I had no idea it is that faster:

--CONSTRAINT dateonly CHECK (convert(char(8 ), birthdate, 112) =
birthdate)

DECLARE @d1 DATETIME, @d2 DATETIME, @i INT, @cnt INT
DECLARE @d TABLE(ddd DATETIME)
SET NOCOUNT ON

SET @i = 0
WHILE @i<100000 BEGIN
INSERT @d VALUES('2006010 1')
SET @i = @i + 1
END
SET @d1 = GETDATE()
SET @i = (SELECT COUNT(*) FROM @d WHERE (convert(char(8 ), ddd, 112) =
ddd))
SET @d2 = GETDATE()
SELECT DATEDIFF(ms, @d1, @d2), 'char'

SET @d1 = GETDATE()
SET @i = (SELECT COUNT(*) FROM @d WHERE
(dateadd(d,date diff(d,'1990-01-01',ddd),'1990-01-01') = ddd))
SET @d2 = GETDATE()
SELECT DATEDIFF(ms, @d1, @d2), 'datediff'

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

----------- ----
346 char

----------- --------
46 datediff

**Erland Sommarskog** · Sep 20 '06, 06:45 AM

Re: formatting for a birth date field??

Alexander Kuznetsov (AK_TIREDOFSPAM @hotmail.COM) writes:

I did a quick benchmarking. I always knew that DATEDIFF approach should
be faster than converting to CHAR, but I had no idea it is that faster:

Yeah, I know. Other people has been suggesting that as well. I just keep
looking it and saying to myself "what on earth does that do?".

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**Hugo Kornelis** · Sep 20 '06, 09:55 PM

Re: formatting for a birth date field??

On Wed, 20 Sep 2006 07:00:14 +0000 (UTC), Erland Sommarskog wrote:

>I just keep
>looking it and saying to myself "what on earth does that do?".

Hi Erland,

It's actually quite simple. It counts the number of days between a
randomly chosen pivot date and the input date, then counts that number
of days from the pivot date to arrive back at the input date.

A: "Hey, do you know how many days have passed since Jan 1st?"
B: "That would be 263."
A: "Okay. Next question: what date is 263 days after Jan 1st?"
B: "Hey, stupid, that would be today, of course. Sept 21. Couldn't you
just have asked what day it is instead of these silly calculations?"
A: "Could have, but knowing you, you would have told me the time as
well. I wanted just the date."

--
Hugo Kornelis, SQL Server MVP

formatting for a birth date field??

formatting for a birth date field??

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment