Convert given Date to various formats

Question

Convert given Date to various formats

mbvnaveen5

SSC Veteran

Points: 262
More actions
July 23, 2013 at 12:12 am

#280357

I have declare Start Date in below format.
Declare @StartDate date = '2013-01-01'
I want get output for below 2 col's as follows
DateKey(int) = 01012013
Date(date) = '01-01-2013'
INT, Date are datatypes here

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

mbvnaveen5 (7/23/2013)
I have declare Start Date in below format.
Declare @StartDate date = '2013-01-01'
I want get output for below 2 col's as follows
DateKey(int) = 01012013
Date(date) = '01-01-2013'
INT, Date are datatypes here

First, you won't get a leading zero for the INT version of the date. Second, the date will be stored the same way in the Date column as it is in the @StartDate variable.

mbvnaveen5 SSC Veteran Points: 262 More actions · Answer 2

Thank You Lynn Pettis for your reply

This is the part of populating Time Dimension

1) Without leading zero, is it possible to get it as 1012013,2012013.....10012013 for DateKey column. How to achieve this?

2) Can we convert the value in @StartDate and make output as 01-01-2013 ? or any other alternative for this

mbvnaveen5 SSC Veteran Points: 262 More actions · Answer 3

Thank You Lynn Pettis for reply

This is the part of populating Time Dimension

1) Without leading zero, is it possible to get it as 1012013,2012013.....10012013 for DateKey Column. How to achieve this?

2) Can we convert the value in @StartDate and make output as 01-01-2013 ? or any other alternative for this

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

You can easily use convert to force the formatting.

Declare @StartDate date = '2013-01-01'

select @StartDate, REPLACE(CONVERT(varchar, @StartDate, 110), '-', '') as IntVersion, CONVERT(varchar, @StartDate, 110) as DateVersion

The real concern is that you seem to be confused with datatypes here. What this code does is presentation, it has NO bearing on how that data is stored. In other words, the "int" version is not an int. It can't be an int because of the leading zeros. The "date" version is also only a string representation of a date datatype.

_______________________________________________________________

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/