October 4, 2012 at 12:04 am
hey guys,
having a bit of an issue and can't work it out any assistance would be great.
I have a column called ImportDate which is a varchar(8) and data looks like 10122012
I need to convert it to a Date format only as 20121210
Im using SQL2005 database not 08 but running Management Studio 2008.
I got something like but get errors.
CAST(CONVERT(VARCHAR(8),ImportDate,112) AS DATETIME)
Thanks for your help
October 4, 2012 at 12:15 am
Please post the error you are getting
October 4, 2012 at 12:16 am
Msg 242, Level 16, State 3, Line 34
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
October 4, 2012 at 12:17 am
You can try something like this:
SET DATEFORMAT DMY
SELECT CAST(STUFF(STUFF('10122012', 5, 0, '-'), 3, 0, '-') AS DATETIME)
SET DATEFORMAT MDY
Replace the literal date with your ImportDate.
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
October 4, 2012 at 12:22 am
i believe if you want to convert to the format yyyymmdd then the tsql should be like
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD].
if you are stll gettingthe error you can check your date format in regional settings .
October 4, 2012 at 12:28 am
It still comes back as 15122004 which is how it originally is stated. using that convert
the CAST(STUFF(STUFF syntax worked but trying to do it as a convert like if possible similar to how i currently have it.
October 4, 2012 at 12:37 am
Tava (10/4/2012)
the CAST(STUFF(STUFF syntax worked but trying to do it as a convert like if possible similar to how i currently have it.
It works but you don't want to use it because you want to do something else? :w00t::hehe: Dude, you're a tough audience.
The problem is CONVERT isn't recognizing the VARCHAR field with the characters in the order they're being presented, so you may not be able to find a combination that works for you (sorry, I don't have time to research that myself).
The other option is to simply rearrange the characters yourself, which is a lot more verbose:
SET DATEFORMAT DMY
DECLARE @ImportDate VARCHAR(8) = '10122012'
SELECT CAST(STUFF(STUFF(@ImportDate, 5, 0, '-'), 3, 0, '-') AS DATETIME)
SET DATEFORMAT MDY
-- Or without changing dateformat
SELECT CAST(SUBSTRING(@ImportDate, 5, 4) +
SUBSTRING(@ImportDate, 3, 2) +
SUBSTRING(@ImportDate, 1, 2) AS DATETIME)
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
October 4, 2012 at 12:37 am
did u checked ur regional settings ??
October 4, 2012 at 12:41 am
sorry 🙂 it was more to see of a way to see if i can understand it better if you know what i mean the STUFF syntax confused me, while the other syntax i can sort of get my head around.
Thanks for your help on this
October 4, 2012 at 12:45 am
BTW. Did you try running this?
DECLARE @ImportDate VARCHAR(8) = '10122012'
SELECT CONVERT(VARCHAR(8),@ImportDate,112)
You'll notice absolutely no change to the string being converted. That's because it is already a VARCHAR(8), i.e., CONVERT knows it needs to do nothing. 112 is a format code used to convert a DATETIME datatype to a VARCHAR.
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
October 4, 2012 at 12:53 am
Regional settings were dd mm yyyy but i dont want to change it .... STUFF Reply helps me there.
i tried the variable way but im going to stick with the STUFF - i should learn what the syntax does and how it works so im not just limited to one solution. i.e convert etc
thanks for your help
October 4, 2012 at 1:03 am
If it's a learning expedition you're on, try this:
SET DATEFORMAT DMY
DECLARE @ImportDate VARCHAR(8) = '10122012'
SELECT CAST(STUFF(STUFF(@ImportDate, 5, 0, '-'), 3, 0, '-') AS DATETIME)
,CAST(CAST(10000*(@ImportDate%10000) +
100*((@ImportDate/10000)%100) +
@ImportDate/1000000 AS VARCHAR) AS DATETIME)
,CAST(LEFT(10000*(@ImportDate%10000) +
100*((@ImportDate/10000)%100) +
@ImportDate/1000000, 8) AS DATETIME)
SET DATEFORMAT MDY
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
October 4, 2012 at 6:03 pm
DECLARE @ImportDate VARCHAR(8) = '10122012'
SELECT CAST(
RIGHT(@ImportDate , 4) + --Year
SUBSTRING(@ImportDate , 3 , 2) + --Month
LEFT(@ImportDate , 2) --Day
AS DATE)
Wes
(A solid design is always preferable to a creative workaround)
October 4, 2012 at 7:34 pm
How to set Regional settings?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply