converting datetime to 112 type

Question

converting datetime to 112 type

KoldCoffee

SSCoach

Points: 18971
More actions
November 14, 2014 at 4:16 pm

#312557

I want YYYYMMDD.
If I do this it works:
SELECT CONVERT(varchar(8), GETDATE(), 112)
Gives me this:
20141114
If I treat as parameter it doesn't:
DECLARE @date_start datetime
SET @date_start = CONVERT(varchar(8), GETDATE(), 112)
SELECT @date_start;
Gives me this:
2014-11-14 00:00:00.000
how to pass the converted value in 112 style as a parameter, does anyone know?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

KoldCoffee SSCoach Points: 18971 More actions · Answer 1

I moved the conversion into the query, instead of trying to do it at time of setting the param and now the conversion works.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 2

KoldCoffee (11/14/2014)
I moved the conversion into the query, instead of trying to do it at time of setting the param and now the conversion works.

The problem is that DATETIME cannot be formatted. When you format GETDATE() and stuff it into a DATETIME variable, it's just like doing an explicit conversion back to DATETIME.

Again, you cannot store formatted dates in a DATETIME datatype.

--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)

KoldCoffee SSCoach Points: 18971 More actions · Answer 3

hm, you can't store formatted values in DATETIME variables. good to know there's reason for trouble and it's root reason.

any others like that?

Thanks Jeff!!!

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

Sure there are. For example, if you convert a Money data-type to VARCHAR so that you get commas and then store that back in a Money data-type, the commas go away because you're not storing it in a VARCHAR anymore.

On dates and times, you can do a minor bit of formatting but only what gets returned by changing either the base language or by setting the date format to (for example) DD/MM/YYYY instead of MM/DD/YYYY but, behind the scenes, no formatting was actually done because a DATETIME data-type doesn't ever store formatted data. It simply stores dates and times as two integers behind the scenes.

Shifting gears a bit, it's never a good idea to actually store dates, times, or amounts as formatted VARCHARs because it renders a whole lot of functionality totally useless unless there's either an implicit or an explicit conversion... both of which can be real time wasters and the cause of much unnecessary conversion code. The only time that data should be formatted is either by the front end or when you have to write formatted data to a file. I'd never store formatted data in a permanent table.

--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)