Formatting Date and Time

[luvtitans50]

I have a date field that I need to be formatted in 2 ways:

1 - I need the field to come out as YYYYMMDD, but what I have is leaving out the '0' in the month field as:

Convert(VarChar(4), year(getdate()))+''+CONVERT(VarChar(2),Month(getdate()))+''+Convert(VarChar(2), Day(getdate())) as createdate: 2010219

2- I need the field to come out as HHMMSSNN? How can I accomplish this?

Thanks for any assistance!

 

[djj55]

REPLACE(CONVERT(VARCHAR(10), GETDATE(), 102), '.', '')
REPLACE(CONVERT(VARCHAR(11), GETDATE(), 114), ':', '')

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[luvtitans50]

WOW~ Thanks a million!!

 

[luvtitans50]

One quick question? How can you change the order of the date if you want it to display as:

REPLACE(CONVERT(VARCHAR(10), GETDATE(), 102), '.', ''

YYYYDDMM?

 

[Jamfool]

The date one can also be done like this:

SELECT CONVERT(CHAR(8),GETDATE(),112)

http://msdn.microsoft.com/en-us/library/ms187928.aspx

 

[luvtitans50]

These are very helpful!

Now if I can figure out how to rearrange the order of the date as such: YYYYDDMM that would be awesome?

 

[djj55]

Using 112 should do that for you.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[Jamfool]

you picked a strange format

There are several ways to achieve your end goal concatenating the date parts and adding leading zeros etc.

I would suggest if it is something that you will use in several places that you create it as a function.

Here is one way:

DECLARE @dt datetime 
set @dt = GETDATE()-10

SELECT CAST(YEAR(@dt) AS CHAR(4))+REPLACE(LEFT(CONVERT(CHAR(10),@dt,103),5),'/','')

function:

CREATE FUNCTION [dbo].[YYYYDDMM]  
(@dt DATETIME)
RETURNS VARCHAR(8) AS  
BEGIN 
RETURN CAST(YEAR(@dt) AS CHAR(4))+REPLACE(LEFT(CONVERT(CHAR(10),@dt,103),5),'/','')
END

GO

SELECT [dbo].[YYYYDDMM] ( GETDATE() )

 

[markros]

See also

http://wiki.lessthandot.com/index.php/Data_formatting_dates

though I don't see yyyyddmm format listed there.

PluralSight Learning Library

 

[djj55]

Year day month is a problem, as day month year or month day year are available but year month day is all I see for date formats starting with year. (This being easy to sort).

Writing a function might be your best bet.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[luvtitans50]

Okay, thanks for all of your help with this!!!