January 13, 2010 at 11:13 pm
Hi Guys,
Hoping someone can help me.
Due to a software package we use's way of managing it's database, it seems to use a nvarchar(50) field for it's date... unsure why, but anyway.
In one of the fields, it stores two different formats for dates being:
13 January 2010 09:15:37
and also:
Wednesday, 13 January 2010 11:48:44pm
When i attempt a convert or a cast, i get an errors such as the the following:
Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.
Now obviously this is because it doesn't like the two different date formats.. does anyone have any idea how this can be done?? as the original software cannot be changed, however there must be a way to convert or cast?
January 13, 2010 at 11:26 pm
I think you probably have the first one figured out.
For the second date format, try the following:
Declare @datestring nvarchar(100)
Set @Datestring = 'Wednesday, 13 January 2010 11:48:44pm'
Select CONVERT(datetime,right(@DateString,len(@DateString) - charindex(' ',@datestring)),112)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 13, 2010 at 11:40 pm
Hi There, thanks for getting back to me! After all the time i spent, you got way further then i did... i feel so average now lol
In the date type it works in:
For example:
Wednesday, 13 January 2010 11:48:44pm
Now says:
2010-01-13 23:48:44.000
Is there a reason it shows the extra 000's and can they be removed or are they fine to leave there?
Also i can convert the others (using standard convert), but how to you convert them in one statement so it returns all the results in the query? Or do i need to do something else to combine them?
As the others now have (the date is broken):
13 January 2010 18:04:31
are
2010-01-01 18:04:31.000
The reason i'm doing this is to do a:
date < getdate() - 2
(which apparently date diff does much better...)
January 13, 2010 at 11:46 pm
adam 19559 (1/13/2010)
Hi There, thanks for getting back to me! After all the time i spent, you got way further then i did... i feel so average now lolIn the date type it works in:
For example:
Wednesday, 13 January 2010 11:48:44pm
Now says:
2010-01-13 23:48:44.000
Is there a reason it shows the extra 000's and can they be removed or are they fine to leave there?
You can leave that as is. That is the precision.
Also i can convert the others (using standard convert), but how to you convert them in one statement so it returns all the results in the query? Or do i need to do something else to combine them?
As the others now have (the date is broken):
13 January 2010 18:04:31
are
2010-01-01 18:04:31.000
The reason i'm doing this is to do a:
date < getdate() - 2
(which apparently date diff does much better...)
Are both date formats stored in the same table.column?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 13, 2010 at 11:51 pm
Hello again!
Yes both formats are stored in the same column (ugh...) and they both need to change. It's horribly frustrating...
table name: KPCourseDetails
column: lessondate
January 14, 2010 at 12:01 am
adam 19559 (1/13/2010)
Hello again!Yes both formats are stored in the same column (ugh...) and they both need to change. It's horribly frustrating...
table name: KPCourseDetails
column: lessondate
This will probably work (so long as there are no other anomalies).
Declare @datestring nvarchar(100)
--Set @Datestring = 'Wednesday, 13 January 2010 11:48:44pm'
Set @Datestring = '13 January 2010 09:15:37'
select charindex('day, ',@DateString)
Select case when charindex('day, ',@DateString) > 0
then CONVERT(datetime,right(@DateString,len(@DateString) - charindex(' ',@datestring)),112)
else CONVERT(datetime,@DateString,112)
End
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 14, 2010 at 12:02 am
You would obviously need to make some modifications to make it work with your table. I left the variable in there just for testing purposes and demonstration purposes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 14, 2010 at 3:36 pm
Hi There,
Thanks for getting back to me again.
My current query is:
SELECT lessondate, CONVERT(datetime,right(lessondate,len(lessondate) - charindex(' ',lessondate)),112), dateserial FROM KPCourseDetails
WHERE coursename = 'Provide Responsible Service of Alcohol (QLD)' AND timesattempted = 4 AND lessonstatus = 'failed'
It's so embarassing.. but how would i combine them?
January 14, 2010 at 3:40 pm
as a test i've set up:
SELECT lessondate,
case
when charindex('day, ',lessondate) > 0
then CONVERT(datetime,right(lessondate,len(lessondate) - charindex(' ',lessondate)),112)
else CONVERT(datetime,lessondate,112)
FROM KPCourseDetails
WHERE coursename = 'Provide Responsible Service of Alcohol (QLD)' AND timesattempted = 4 AND lessonstatus = 'failed'
And i get:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'FROM'.
Same if i try:
select case when charindex('day, ',lessondate) > 0
then CONVERT(datetime,right(lessondate,len(lessondate) - charindex(' ',lessondate)),112)
else CONVERT(datetime,lessondate,112)
FROM KPCourseDetails
I get:
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'FROM'.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply