rustypoot
asked on
SQL Server 2008 Date Conversion
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
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
What is the datatype of column MSH7DateTime.
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?
What is the datatype of MSH7DateTime column? Is it varchar?
What is the exact problem you are facing?
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.
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.
ASKER
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)+'/'+SUBS TRING (cast(MSH7DateTime as varchar(50)),7,2) + '/'
+left(cast(MSH7DateTime as varchar(50)),4)) - 1
ORDER BY MSH7DateTime
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)+'/'+SUBS
+left(cast(MSH7DateTime as varchar(50)),4)) - 1
ORDER BY MSH7DateTime
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
and you wish this format mm/dd/yyyy
try
select SUBSTRING (cast(MSH7DateTime as varchar(50)),5,2)+'/'+SUBS
from RejectedMessages