formatting for a birth date field??

[tlyczko]

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

[David Portas]

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.Typically 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:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Hugo Kornelis]

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]

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.Typically 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]

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]

tlyczko (tl*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[tlyczko]

Erland Sommarskog wrote:

tlyczko (tl*****@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]

tlyczko (tl*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[tlyczko]

Erland Sommarskog wrote:

tlyczko (tl*****@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]

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('20060101')
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,datediff(d,'1990-01-01',ddd),'1990-01-01') = ddd))
SET @d2 = GETDATE()
SELECT DATEDIFF(ms, @d1, @d2), 'datediff'

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

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

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

[Erland Sommarskog]

Alexander Kuznetsov (AK************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

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