January 28, 2013 at 10:43 am
How do i convert the below date from
Mon Jan 28 11:03:06 EST 2013
To
2013-01-28 11:03:06
Thanks for your help in advance
January 28, 2013 at 11:10 am
Mvs2k11 (1/28/2013)
How do i convert the below date fromMon Jan 28 11:03:06 EST 2013
To
2013-01-28 11:03:06
Thanks for your help in advance
Are you trying to take a string and turn it into a datetime? The source you posted will not directly convert to a datetime. You are going to have to tweak this a bit make it work. What is the source of this date? Is it a datetime datatype or is it string data?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 28, 2013 at 11:15 am
This works on your example provided.
declare @String varchar(50) = 'Mon Jan 28 11:03:06 EST 2013'
select cast(left(right(@String, len(@String) - 4), 16) + right(@String, 4) as DateTime)
Depending on how things like single digit days are in the source this may not work. For example if the source contains 'Mon Jan 8 11:03:06 EST 2013' for January 8th this won't work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 28, 2013 at 1:08 pm
select
convert(varchar(20), cast(stuff(substring(string, 5, 15), CHARINDEX(':', substring(string, 5, 15)) - 3, 0, RIGHT(string, 5)) as datetime), 120)
from (
select 'Mon Jan 28 11:03:06 EST 2013' as string union all
select 'Mon Jan 8 11:03:06 EST 2013'
) as test_data
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 28, 2013 at 4:42 pm
Thanks a lot.
How can i extend the query to pull the records fromdate and to date..
January 28, 2013 at 4:51 pm
Not 100%, as I don't have any table definition to look at.
But if fromdate and todate are both in the text format above, then something like:
SELECT
...other_columns...
CONVERT(varchar(20), CAST(STUFF(SUBSTRING(fromdate, 5, 15),
CHARINDEX(':', substring(fromdate, 5, 15)) - 3, 0, RIGHT(fromdate, 5)) as datetime), 120) AS from_date,
CONVERT(varchar(20), CAST(STUFF(SUBSTRING(todate, 5, 15),
CHARINDEX(':', substring(todate, 5, 15)) - 3, 0, RIGHT(todate, 5)) as datetime), 120) AS to_date,
...other_columns...
FROM dbo.tablename
--INNER JOIN ...
WHERE ...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 28, 2013 at 5:27 pm
Mvs2k11 (1/28/2013)
How do i convert the below date fromMon Jan 28 11:03:06 EST 2013
To
2013-01-28 11:03:06
Thanks for your help in advance
If the string contains EST, does that mean other strings might contain different time zone offsets?
That would be a challenging opportunity to use the DATETIMEOFFSET data type.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 29, 2013 at 6:01 pm
Yes you are correct the other strings contains different time zone.
January 29, 2013 at 6:19 pm
Mvs2k11 (1/29/2013)
Yes you are correct the other strings contains different time zone.
If you'd like me to take a shot at implementing my suggestion, can you provide some additional sample data with the time zone information included as it would appear in your input strings? Like CST, MST, PST and/or EDST, CDST, etc.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply