Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date conversion issue Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2012 3:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 8:06 AM
Points: 167, Visits: 414
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
Posted Wednesday, September 26, 2012 3:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 7,127, Visits: 13,503
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1364528
Posted Wednesday, September 26, 2012 7:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 246, Visits: 620
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
Posted Wednesday, September 26, 2012 7:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 7,127, Visits: 13,503
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1364668
Posted Wednesday, September 26, 2012 7:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 246, Visits: 620
Thanks!
Post #1364695
Posted Wednesday, September 26, 2012 8:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 6, 2014 11:35 PM
Points: 75, Visits: 262
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 »

Add to briefcase

Permissions Expand / Collapse