Date conversion issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Date conversion issue

Date conversion issue

Rate Topic

Display Mode

Topic Options

Author

Message

saidapurs

Posted Wednesday, September 26, 2012 3:29 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:46 AM
Points: 167, Visits: 413

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

Post #1364521

ChrisM@Work

Posted Wednesday, September 26, 2012 3:39 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

saidapurs (9/26/2012)

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1364528

tim_harkin

Posted Wednesday, September 26, 2012 7:08 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 138, Visits: 358

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

Post #1364640

ChrisM@Work

Posted Wednesday, September 26, 2012 7:32 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

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}

Post #1364668

tim_harkin

Posted Wednesday, September 26, 2012 7:53 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 138, Visits: 358

Thanks!

Post #1364695

swaroop.sahoo

Posted Wednesday, September 26, 2012 8:48 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:36 AM
Points: 75, Visits: 255

Hi,

I think this will help

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

Thanks,
Swaroop

Post #1364754

« Prev Topic | Next Topic »

Permissions