February 22, 2013 at 1:26 am
All,
I have a table as below.
create table date_rd
(
date varchar(50)
)
insert into date_rd
select 'Mon Sep 17 00:00:00 CEST 2012'
insert into date_rd
select 'Nov 27 2012 10:14AM'
insert into date_rd
select 'Sep 18 2012 12:00AM'
insert into date_rd
select 'Oct 15 2012 12:00AM'
insert into date_rd
select 'Oct 15 2012 12:00AM'
insert into date_rd
select 'Sat Feb 09 17:16:18 IST 2013'
I have to convert the above date into YYYY-MM-DD hh:mm:ss format.
Inputs are welcome!
karthik
February 22, 2013 at 2:29 am
Hi Karthik,
I think you should convert 'Mon Sep 17 00:00:00 CEST 2012' & 'Sat Feb 09 17:16:18 IST 2013' to datetime format string before inserting data into your table.
Because there is no date format like " 'Sat Feb 09 17:16:18 IST 2013'" "IST" but we can convert our output into any desirable format.
You can consider any specific string format, then It will be easy to convert to any format. If you change the before inserting into table, it is only one time task & you can do it with the help of "case" statement.
February 22, 2013 at 5:01 am
yes. You are correct. We have changed the same in UI. The date will come in the expected format in future. But for the existing one, I have to update as per the requested format.
karthik
February 22, 2013 at 5:28 am
I hope this can help you.
SELECT CONVERT(DATETIME, LTRIM(RTRIM(A.FormattedDate)))
FROM
(
SELECT CASE
WHEN PATINDEX('%CEST%', dr.date) > 0 THEN SUBSTRING(dr.date, PATINDEX('%CEST%', dr.date)+5, 4) + ' ' + SUBSTRING(dr.date, 5, (PATINDEX('%CEST%', dr.date)-5))
WHEN PATINDEX('%IST%', dr.date) > 0 THEN SUBSTRING(dr.date, PATINDEX('%IST%', dr.date)+4, 4) + ' ' + SUBSTRING(dr.date, 5, (PATINDEX('%IST%', dr.date)-5))
ELSE dr.date
END AS FormattedDate
FROM dbo.date_rd dr
) A
feel free to add more time zone conditions according to your data.
February 22, 2013 at 5:34 am
one more thing which forgot to include the Time Difference between IST & GMT.
It depends on your DB server/you that in which time zone you are saving your date time data.
If You are using GMT, then you can add/subtract required hours/minutes in each row before updating your data.
I hope you can easily find it with above code. If you face any problem, let me know.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply