We help IT Professionals succeed at work.

SQL Server 2008 Date Conversion

rustypoot
rustypoot used Ask the Experts™
on
I have this SQL;

select Convert(varchar, MSH7DateTime, 101)  from RejectedMessages

This returns the date in this format - 20120413150637

So my conversion is not working; can anyone please suggest any other way or why the conversion is not working?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
if your data for MSH7DateTime like this one 20120413150637
and you wish this format mm/dd/yyyy

try

select SUBSTRING (cast(MSH7DateTime as varchar(50)),5,2)+'/'+SUBSTRING (cast(MSH7DateTime as varchar(50)),7,2) + '/' +left(cast(MSH7DateTime as varchar(50)),4)
from RejectedMessages

Commented:
What is the datatype of column MSH7DateTime.

Commented:
What is the format in which you want your output in?
What is the datatype of MSH7DateTime column? Is it varchar?
What is the exact problem you are facing?

Commented:
In order for the 101 to make its conversion, you have to have a Datetime column-type.

If it is a string, then it will just put it in the varchar unchanged.

CONVERT will not convert the string you currently have, into a date, so you will have to either use the SUBSTRING method or split the value in half and make it a date convertable string.

Author

Commented:
Ok I tied this and it errored out. I am trying to pull data for yesterday.

SELECT * FROM RejectedMessages
WHERE ErrorMessage LIKE 'Error:Invalid ADM - Item Usage Update - FT1.7.1%'
AND MSH7DateTime = (SUBSTRING (cast(MSH7DateTime as varchar(50)),5,2)+'/'+SUBSTRING (cast(MSH7DateTime as varchar(50)),7,2) + '/'
+left(cast(MSH7DateTime as varchar(50)),4)) - 1
ORDER BY MSH7DateTime
Top Expert 2012

Commented:
Ok I tied this and it errored out. I am trying to pull data for yesterday.
Is there any chance you can answer the questions:
What is the data type for MSH7DateTime?
What error message are you getting?

Incidentally, stating that you do not know or do not wish to tell us is a perfectly viable option.  It just tells us you are not serious about getting an answer and we can go and help someone else.
Commented:
You could try this:

SELECT * FROM RejectedMessages
WHERE ErrorMessage LIKE 'Error:Invalid ADM - Item Usage Update - FT1.7.1%'
AND CONVERT(DATETIME, LEFT(MSH7DateTime, 8)) = CONVERT(datetime, CONVERT(varchar(20), DATEADD(day, -1, GETDATE()), 101))
ORDER BY MSH7DateTime

Author

Commented:
Thanks