Date conversion issue

Question

Date conversion issue

saidapurs

Hall of Fame

Points: 3477
More actions
September 26, 2012 at 3:29 am

#267245

Hi All,
Using below conversion i am getting date only like 20120926 but my requirement is 201209261015 like hour & time
convert(nvarchar(50),(GetDate()),112))
Please help me to get my exact answer...
Thanks & Regards
Satish

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

saidapurs (9/26/2012)
Hi All,
Using below conversion i am getting date only like 20120926 but my requirement is 201209261015 like hour & time
convert(nvarchar(50),(GetDate()),112))
Please help me to get my exact answer...
Thanks & Regards
Satish

Style 112 excludes time, returning only date.

See CONVERT in Books Online for more information.

There is no value for Style which will return datetime in the format you require,

you will have to use something like 120, then remove the unwanted characters.

You don't need a double-byte character set to hold numbers.

Match your variable to your data - '201209261015' is CHAR(12)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tim_harkin SSCrazy Points: 2399 More actions · Answer 2

Not the most efficient solution....

DECLARE @currentTime DATETIME

SET @currentTime = GETDATE()

SELECT CAST(DATEPART(year,@currentTime) AS CHAR(4))

+ CASE WHEN (DATEPART(month,@currentTime)) > 10 then (CAST(DATEPART(month,@currentTime) AS CHAR(2)))

ELSE '0' + CAST(DATEPART(month,@currentTime) AS CHAR(1)) END

+ CASE WHEN (DATEPART(day,@currentTime)) > 10 THEN (CAST(DATEPART(day,@currentTime) AS CHAR(2)))

ELSE '0' + CAST(DATEPART(day,@currentTime) AS CHAR(1)) END

+ CASE WHEN (DATEPART(hour,@currentTime)) > 10 THEN (CAST(DATEPART(hour,@currentTime) AS CHAR(2)))

ELSE '0' + CAST(DATEPART(hour,@currentTime) AS CHAR(1)) END

+ CASE WHEN (DATEPART(minute,@currentTime)) > 10 THEN (CAST(DATEPART(minute,@currentTime) AS CHAR(2)))

ELSE '0' + CAST(DATEPART(minute,@currentTime) AS CHAR(1)) END

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

tim_harkin (9/26/2012)
Not the most efficient solution....
DECLARE @currentTime DATETIME
SET @currentTime = GETDATE()
SELECT CAST(DATEPART(year,@currentTime) AS CHAR(4))
+ CASE WHEN (DATEPART(month,@currentTime)) > 10 then (CAST(DATEPART(month,@currentTime) AS CHAR(2)))
ELSE '0' + CAST(DATEPART(month,@currentTime) AS CHAR(1)) END
+ CASE WHEN (DATEPART(day,@currentTime)) > 10 THEN (CAST(DATEPART(day,@currentTime) AS CHAR(2)))
ELSE '0' + CAST(DATEPART(day,@currentTime) AS CHAR(1)) END
+ CASE WHEN (DATEPART(hour,@currentTime)) > 10 THEN (CAST(DATEPART(hour,@currentTime) AS CHAR(2)))
ELSE '0' + CAST(DATEPART(hour,@currentTime) AS CHAR(1)) END
+ CASE WHEN (DATEPART(minute,@currentTime)) > 10 THEN (CAST(DATEPART(minute,@currentTime) AS CHAR(2)))
ELSE '0' + CAST(DATEPART(minute,@currentTime) AS CHAR(1)) END

Tim, here's a trick for you;

SELECT CAST(DATEPART(year,@currentTime) AS CHAR(4))

+ RIGHT('0'+CAST(DATEPART(month,@currentTime) AS VARCHAR(2)),2)

+ RIGHT('0'+CAST(DATEPART(day,@currentTime) AS VARCHAR(2)),2)

+ RIGHT('0'+CAST(DATEPART(hour,@currentTime) AS VARCHAR(2)),2)

+ RIGHT('0'+CAST(DATEPART(minute,@currentTime) AS VARCHAR(2)),2)

Here's a quick way to meet OP's requirement;

SELECT CAST(REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),@currentTime,120),'-',''),' ',''),':','') AS CHAR(12))

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tim_harkin SSCrazy Points: 2399 More actions · Answer 4

tim_harkin

SSCrazy

Points: 2399

September 26, 2012 at 7:53 am

#1542419

Thanks!

swaroop.sahoo SSChasing Mays Points: 617 More actions · Answer 5

Hi,

I think this will help

SELECT CONVERT(VARCHAR(10), GETDATE(), 112) + Replace(CONVERT(VARCHAR(10), GETDATE(), 108),':','')

Thanks,

Swaroop