Date Time Formatting using Concatenation
-
Hello,
I'm writing an application which needs to get datetime fields in a particular format of YYYYMMDDHHMMSSMM. These numbers can then be compared and operated on in my software.
An example of what I'm currently doing is as follows
USE pubs
GO
SELECT
CAST(DATEPART(yyyy,pubdate) AS VARCHAR(4)) +
CAST(DATEPART(mm,pubdate) AS VARCHAR(4)) +
CAST(DATEPART(dd,pubdate) AS VARCHAR(4)) +
CAST(DATEPART(hh,pubdate) AS VARCHAR(4)) +
CAST(DATEPART(mi,pubdate) AS VARCHAR(4)) +
CAST(DATEPART(ss,pubdate) AS VARCHAR(4)) +
CAST(DATEPART(ms,pubdate) AS VARCHAR(4))
FROM titles
GO
My problem is if any of rhe DATEPART values are less than 10, no leading 0 is included. I really need those leading 0's.
Can anybody help me out with this. or suggest a better way to get the datetime formatted the way I want?
Thanks,
Allan.
-
Use CONVERT with a style parameter of 121, then just replace all the chars you don't need.
SELECT REPLACE( REPLACE( REPLACE( REPLACE( CONVERT(varchar(35), GetDate(), 121) , '.', '') , ':', '') , '-', '') , ' ', '')
--------------------
Colt 45 - the original point and click interface
-
That works just fine. Thanks very much.
-
I see you got your answer to the question but I wanted to provide you a sidenote for your leading zero question.
I have used RIGHT function for leading zero adds:
DECLARE @var1 varchar(3)
SET @var1 = '1'
SET @var1 = RIGHT('0' + @var1,2)
PRINT @var1
SET @var1 = '15'
SET @var1 = RIGHT('0' + @var1,2)
PRINT @var1
-
Cool, will bare that in mind. I'm sure it will come in handy.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply