November 20, 2012 at 9:03 am
Hello fellow developers I have the following code which translates MY_COLUMN from decimal to varchar ,then from varchar to Datetime.
The only glitch is that the output is YYYY-MM-DD HH:MM:SS and I want it in MM/DD/YYYY FORMAT without the HH:MM:SS ,
sELECT
CASE WHEN MY_DATE_COLUMN> 19000000 AND MY_DATE_COLUMN < 21000000 THEN
CONVERT(DATETIME,CONVERT(VARCHAR,CONVERT(INT,MY_DATE_COLUMN)))
ELSE CONVERT(DATETIME,'01/01/1900')
END NEW_DATE_COLUMN
FROM MY_TABLE
Thnx
SM
November 20, 2012 at 9:28 am
SQL server rokee (11/20/2012)
Hello fellow developers I have the following code which translates MY_COLUMN from decimal to varchar ,then from varchar to Datetime.The only glitch is that the output is YYYY-MM-DD HH:MM:SS and I want it in MM/DD/YYYY FORMAT without the HH:MM:SS ,
sELECT
CASE WHEN MY_DATE_COLUMN> 19000000 AND MY_DATE_COLUMN < 21000000 THEN
CONVERT(DATETIME,CONVERT(VARCHAR,CONVERT(INT,MY_DATE_COLUMN)))
ELSE CONVERT(DATETIME,'01/01/1900')
END NEW_DATE_COLUMN
FROM MY_TABLE
Thnx
SM
You need to take a look at the CONVERT function carefully.
For example, below takes GETDATE() and gives back a string of "11/20/2012". The magic is all in the style parameter in CONVERT. If you are doing straight conversions, use CAST instead. If you need to twist the results, CONVERT gives more options.
SELECT CONVERT(VARCHAR,GETDATE(),101)
November 20, 2012 at 10:37 am
You also should not keep creating new threads for the same thing. Your answers are all over the place. It would be far easier to use the same thread for a problem all the way through. I know you have been given answers for some of your questions in the various threads you have. I am pretty sure this one was answered in one of them.
_______________________________________________________________
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/
November 20, 2012 at 10:40 am
ok..Will keep that in mind
November 20, 2012 at 12:19 pm
Same thing I say every time this comes up:
Don't do date formatting in the data layer. Do it in the application. Works MUCH better there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 20, 2012 at 12:28 pm
You are right..thank u
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply