Converting date type
-
I have several columns with dates in the following format.
14.08.1924
How do I change the format so that I can perform operations like YEAR(Date)?
-
Cool, I get to misuse Parsename for two different issues in the same day.
DECLARE
@GDate varchar(10)
SET
@GDate = '14.08.1924'
SELECT
Cast(Parsename(@GDate,1) + Parsename(@GDate,2) + Parsename(@GDate,3) AS datetime)
-
David,
is Parsename so efficient that you prefer it over standard conversion?
I would prefer this:
SELECT CONVERT(datetime,'14.08.1924',104)
Mark,
you could also consider changing the datatype of the column to DATETIME if possible, or maybe add another column that would store the datetime value if you can't change the existing. Dates in stored in VARCHAR format don't help performance.
-
Not at all, and based on what I just learned, I'd definitely go with your method. I've never had to do a string to date conversion from a string that wouldn't work with a simple cast (which won't work with a German date in the U.S.), so I never once noticed that small footnote in the Convert function's BOL entry:
"** Input when converting to datetime; output when converting to character data."
I've always considered Convert's style code purely for output purposes, so thanks for teaching an old dog new tricks!
-
Spot on, Vladan
--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) -
The funny thing is that I actually have that Convert styles table from BOL, including the footnote, printed out right in front of my face, as I often have to output to various formats. The only inputs I receive, even though varied, are all recognizable by my server as dates.
Learning something new is fun.
-
Um... be careful in that assumption... while this works...
DECLARE @SomeDate DATETIME
SET @SomeDate = '05.24.2007'
PRINT @SomeDate
...this will not...
DECLARE @SomeDate DATETIME
SET @SomeDate = '24.05.2007'
PRINT @SomeDate
...because the implicit format is understood to be mm.dd.yyyy.
--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) -
> because the implicit format is understood to be mm.dd.yyyy.
On your server.
_____________
Code for TallyGenerator -
Jeff, we get dates in exactly three formats, MM-DD-YYYY, MM/DD/YYYY, and YYYYMMDD (this one has to be a string to work, but is in our case), all of which convert fine without specifying an input format. If we do get dates in another format, I'll start using the input method of the Convert function that Vladen outlined, as that works like a charm. We output it in all sorts of formats, some of which require various sorts of string manipulation, but nothing so fancy in the imports, which we do control in house.
-
Yeah, shame on me... I keep forgetting about that...
On other servers, particularly those outside the U.S.A., the reverse failure would be true unless Convert were used.--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) -
Oh, I agree... I'm lucky enough to have a similar situation.
Just wanted everyone to know the implicit "dots" work the same way (month before day) on certain servers and not as date conversion type 104 would suggest (day before month).
--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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply