Generate sql: imported

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, Tomtlyczko wrote:

Quote:

Originally Posted by

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, 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/...US,SQL.90).aspx
--|||On 16 Sep 2006 06:12:07 -0700, tlyczko wrote:

Quote:

Originally Posted by

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

(snip)

Quote:

Originally Posted by

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

(snip)

Quote:

Originally Posted by

>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|||David Portas wrote:

Quote:

Originally Posted by

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|||Hugo Kornelis wrote:

Quote:

Originally Posted by

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|||tlyczko (tlyczko@.gmail.com) writes:

Quote:

Originally Posted by

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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

tlyczko (tlyczko@.gmail.com) writes:

Quote:

Originally Posted by

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|||tlyczko (tlyczko@.gmail.com) writes:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

tlyczko (tlyczko@.gmail.com) writes:

Quote:

Originally Posted by

>
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|||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|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:

Quote:

Originally Posted by

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?".

Quote:

Originally Posted by

>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

I imported data from a text file, where dates are currently in the format: 050729 to represent July 29, 2005.

How could I write a sql statment to put make this show up as 07/29/05?

Thanks

How did you do the import? What is the datatype you currently have for the column?|||It's a varchar now. I tried to convert to a date, but there must be fields somewhere with bad data because it won't let me. The problem is though that this table has over 6 million rows so it's not easy to figure out where the problem is.|||

Your value of 050729 is not clear as to which part of it is month and which part of it is day.

A quick SELECT CONVER(Datetime, '050729') returns 2005-07-2900:00:00.000.Not sure if that is what you want?

|||

The select you showed me is what I was looking for. Could I then do something like this?

update RetailSalesConvert(DateTime,InvoiceDate)

|||

First, is InvoiceDate varchar? Then it makes no sense. You could add another column with Datetime data type (Set it to null initially). Update that row with the converted value from InvoiceDate, then drop the existing column, rename the new column to old.

Here's a series of steps to follow:

--Add a new columnALTER Table RetailSalesADD COLUMN InvoiceDate2DatetimeNULLGo--Update the column with the values converted to datetimeupdate RetailSalesSet InvoiceDate2 =Convert(DateTime,InvoiceDate)GO--Drop the existing InvoiceDateALTER TABLe RetailSalesDROP COLUMN InvoiceDateGo--Rename the new column to InvoiceDatesp_Rename'InvoiceDate2','InvoiceDate'

Wednesday, March 21, 2012

formatting for a birth date field?

Monday, March 19, 2012

Formatting Dates

Formatting Date/Time

Generate sql

Blog Archive

About Me