Converting a Text String to Datetime

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

Database Design and Application Architecture

Converting a Text String to Datetime

Author

Topic

jehrmann
Starting Member

3 Posts

Posted - 2007-09-24 : 11:30:43

Hello all,

New to the forums here. I'm not a beginner with SQL, but nor am I a SQL developer - network engineer who knows some scripting and the fundamentals of DB design/administration. There's the background.

I have a database of client information where a date is keyed in and stored as text. Because this is entered by end-users, the way it's entered varies - 1/1/2001, 01/01/2001, etc. Most use "1/1/2001" (note the date is not the same for each record)

I have 4,000 records to update and I need to try to convert the text string to the correct date - changing 1/1/2005 to the same date in proper date/time format. My database uses datetime as an integer calculating the number of days from 12/30/1800. Today's date would be 75508. Time is separated into different fields.

I can do the work to update the text to a single format to make the conversion easier, but I am having trouble locating the proper way to write a convert function to do this. I've searched online (which is how I came here) and have searched the forums without luck.

Any help would be greatly appreciated!!!

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 11:48:10

CONVERT(datetime, YourDateColumn, 101) if the date is m/d/y sequence, and
CONVERT(datetime, YourDateColumn, 103) if the date is d/m/y sequence

If you've got a mixture of d/m/y and m/d/y then all bets are off!

Kristen

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 11:50:34

An afterthought:

You can also do

SET DATEFORMAT MDY

and then all "convert to date" implicit conversion (in that connect session) will use that date format.


SELECT [1] = CONVERT(datetime, '01/02/2007', 101) -- 2007-01-02
SELECT [2] = CONVERT(datetime, '01/02/2007', 103) -- 2007-02-01

SET DATEFORMAT MDY
SELECT [3] = CONVERT(datetime, '01/02/2007') -- 2007-01-02
SET DATEFORMAT DMY
SELECT [4] = CONVERT(datetime, '01/02/2007') -- 2007-02-01

Kristen

jehrmann
Starting Member

3 Posts

Posted - 2007-09-24 : 14:47:49

Kristen,

Thank you for the fast reply. I tried this against some test data and it worked (with a few syntax errors from the test data), but the script added the time to the end of the new dates, like 00:00:00.0000.

Is this normal?

Also, the other date fields in the database display the number of days since 12/30/1800 as an integer (today - 9/24/2007 is shown as 75508). Could running this possibly create the wrong date when converted to that integer? I would assume not since the DB is configured this, but since the time gets tacked onto the end, I was curious to see if it would convert incorrectly.

Thanks again,

Jeff

jehrmann
Starting Member

3 Posts

Posted - 2007-09-24 : 15:12:37

I should clarify, I tried the first suggestion from Kristen, not the 2nd. All my dates are in mm/dd/yyyy or m/d/y.

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 02:07:57

"Is this normal?"

Afraid so. SQL Server only has a combination DATETIME datatype.

By convention time is stored using the date 01/01/1900.

The Time part of 00:00:00.000 can be ignored by the front end when it displays the data.

You are safe to add, subtract, or find the difference between two dates in integers.

So if you have 75508 since 01/01/1800 then:

SELECT DATEADD(Day, 75508, '18001230')

gives 24-Sep-2007. (I recommend for and date constants in your application you use the 'yyyymmdd' unambiguous form - note: no hyphens!)

Only thing with time you have to watch out for is comparing the difference between two dates that include times.

So "Yesterday" isn't just "Today - 1 day", if "Today" includes the time as well.

You have to do:


WHERE     MyDateColumn >= 'Yesterday @ midnight'
      AND MyDateColumn <  'Today @ midnight'

i.e. the time is stripped off the comparison, so you catch any MyDateColumn with a time somewhere during yesterday. If your MyDateColumn contains NO time, i.e. has a time of 00:00:00.000 then you can just do


WHERE     MyDateColumn = 'Yesterday @ midnight'

Something to watch out for though ...

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-25 : 04:05:43

If the users always enter dates in mdy format, then no problem if your server is set mdy format. Otherwise you need to convert the date values in YYYYMMDD HH:MM:SS format and send to the table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources