Problem with Date - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with Date

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-09-07 : 05:27:03

Hello,

I'm in Query Analyser at the moment but am having problem with one of my fields.

My code is below :

SELECT
UniqueID,
EmployeeNumber,
convert(datetime,[Date], 102) as OTDate,
Details

FROM
dbo.Staging_OT

However, I'm getting an error saying :

Server: Msg 241, Level 16, State 1, Line 4
Syntax error converting datetime from character string.

Can anyone tell me why this is, and how I can get round it please ?

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-07 : 05:34:05

what is the format of your date in the table ?

KH
[spoiler]Time is always against us[/spoiler]

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-09-07 : 05:41:03

It's a varchar(50) in the table and an example of the data in there is : 4/3/2003

However the reason I'm creating this SQL is to take it into my actual table (i.e. not the Staging Table) where the format is smalldatetime.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-07 : 05:46:00

4/3/2003 is MM/DD/YYYY or DD/MM/YYYY ?

use convert(datetime,[Date], 101) as OTDate for MM/DD/YYYY
or convert(datetime,[Date], 103) as OTDate for DD/MM/YYYY

102 is for yy.mm.dd

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 05:47:53

Find the duff dates:

-- SET DATEFORMAT MDY -- Set date format here, if needed
SELECT TOP 100 [Problem date] = [Date], *
FROM dbo.Staging_OT
WHERE IsDate([Date]) = 0

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-07 : 05:48:50

please refer to the Books OnLine or the online version http://msdn2.microsoft.com/en-us/library/ms187928.aspx
for more information

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 05:49:19

Note that if you use

SET DATEFORMAT dmy

you don;t need to worry about using a CONVERT(datetime, string, 999) style fixed-format conversion, the implicit conversion will use the current SET DATEFORMAT

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-07 : 05:49:53

1.5 X

KH
[spoiler]Time is always against us[/spoiler]

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-09-07 : 06:13:46

Hello,

Firstly, thanks for agreeing to help me - I appreciate it.

Secondly, I tried convert(datetime,[Date], 103) as OTDate as advised by Khtan, but got the following error :

==============
(6 row(s) affected)

Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
==============

I try to find the duff dates as advised by Kristen, but I'm not sure what it's meant to show me.
Basically it shows me a "problem date" column with the value "4/22/2003" and a "date" column which has exactly the same value in it.

I'm a bit confused.

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 06:39:26

That means that "4/22/2003" is not recognised as a valid date, so presumably your dates are in Month, Day, Year sequence, so try using DATEFORMAT - i.e.

SET DATEFORMAT MDY
SELECT TOP 100 [Problem date] = [Date], *
FROM dbo.Staging_OT
WHERE IsDate([Date]) = 0

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-07 : 06:42:59

That means your date format is MM/DD/YYYYY you should use convert with style 101

KH
[spoiler]Time is always against us[/spoiler]

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-09-07 : 06:46:36

Thank you both so much - I appreciate all of your help in resolving my problem.

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 06:52:09

"That means your date format is MM/DD/YYYYY you should use convert with style 101"

Nah, you're being optimistic. It means that THAT DATE is M/D/Y ... all the rest may be D/M/Y

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-07 : 08:46:11

quote:
Originally posted by Kristen

That means that "4/22/2003" is not recognised as a valid date, so presumably your dates are in Month, Day, Year sequence, so try using DATEFORMAT - i.e.

SET DATEFORMAT MDY
SELECT TOP 100 [Problem date] = [Date], *
FROM dbo.Staging_OT
WHERE IsDate([Date]) = 0

Kristen

Note that like ISNUMERIC(), ISDATE() is not always reliable

SELECT ISDATE(2000),ISDATE('2000')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources