Convert Varchar to datetime
-
I have a column with values like "050706"
I would like to change this to datetime type. The varchar represents 05 month, 07 date and 06 is the year so in other words it is May 07,2006. I want it in datetime. Any help?
-
This will work as long as your values are all 8 characters in length. Otherwise, you may need to manipulate the string with substring.
declare @string varchar(10)
set @string = '050706'
select @string = stuff(@string,3,0,'/'), @string = stuff(@string,6,0,'/')
select cast(@string as datetime)
-
Nicely done... I'm just a bit squemish about relying on what the default datetime formats have been set to... also, if you replace @String with a column name in the following SELECT, it can be made to work on an entire table at once...
DECLARE @String VARCHAR(10)
SET @String = '050706'
SELECT CONVERT(DATETIME,STUFF(STUFF(@String,5,0,'/'),3,0,'/'),1)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
What's with the '/' delimiters and all? You don't need those (in fact you should avoid them always when writing dates, delimiters are only useful for display and formatting).
There's a small problem though, but it may not be an issue. There's no century, so we have to assume all dates should start with 20 as century?
If so, we have a 6 char string like mmddyy, and we want it to be yyyymmdd.
That's not more complicated than hardcoding yy + the two rightmost digits + the four leftmost.
(we have to hardcode the century since it's not avalaible)
Then cast that string as a datetime if you like, or just insert it into a datetime column.
(I'm assuming that the purpose is to write the date to a datetime datatype, as I understand the question)
declare @bad_date char(6)
set @bad_date = '050706'
select cast('20' + right(@bad_date, 2) + left(@bad_date, 4) as datetime)
--------
20060507
... or replace variable @bad_date with the column name.
-- edit
if century could be both 20 or 19, then hardcoding it won't do, but perhaps the automagic century-cutoff setting would suffice to insert the correct century for each date.
/Kenneth
-
Yep... you can do that... like you said though, the automagic century-cutoff should do the trick but even that can be wrong if these dates are DOB's.
The slashes were an attempt to keep from doing a concatenation but I'm not so sure that double-stuffing is any faster than the parse you've done. I'll have to test that one of these days...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
When converting strings to dates it is useful to be aware of the "Unseparated String Format". You can look up the details of this in BOL.
-
Yep... but won't work here... the original poster said the date formats where in a mmddyy format... here's what BOL says about the "Unseparated String Format".... I've highlighted the important part, in this case...
Unseparated String Format
Microsoft® SQL Server™ 2000 allows you to specify date data as an unseparated string. The date data can be specified with four, six, or eight digits, an empty string, or a time value without a date value.
The SET DATEFORMAT session setting does not apply to all-numeric date entries (numeric entries without separators). Six- or eight-digit strings are always interpreted as ymd. The month and day must always be two digits.
This is the valid unseparated string format:
[19]960415A string of only four digits is interpreted as the year. The month and date are set to January 1. When specifying only four digits, you must include the century.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff:
I had not defined how it would work, I was simply asserting that it is a useful format to be aware of when converting strings to dates. You may or may not agree with that assertion!
I feel it is useful because 6 or 8 digit strings are always interpreted as ymd.
Obviously in respect of the problem as stated, some string manipulation would still be required and there is the "century" issue to consider. These points had been covered prior to my post. However, the Unseparated String Format negates the need for slashes and explicit casting/converting.
-
Wasn't an attack on your post Julian (I shouldn't have made things so bold, "sounds" like I'm yelling
). Nope... I was just stating that it wouldn't work in this case. As you suggest, the Unseparated String Format is quite useful especially when in the ISO format of YYMMDD or YYYYMMDD.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply