Question

I need date string using sql statement like..

select getDate()

this will return 2010-06-08 16:31:47.667

but I need in this format 201006081631 = yyyymmddhoursmin

How can I get this?

Thanks

Answer 1

One way

select left(replace(replace(replace(
   convert(varchar(30),getDate(),120),   ,  ), - ,  ), : ,  ),12)

or like this

select replace(replace(replace(
   convert(varchar(16),getDate(),120),   ,  ), - ,  ), : ,  )

or

select convert(varchar(8), getdate(),112) + 
   (replace(convert(varchar(5), getdate(),108), : ,  ))

See also: CAST and CONVERT (Transact-SQL)

Answer 2

Another way...

DECLARE @d DATETIME

SELECT @d =  2010-06-09 1:37:58.030 


Select Convert(BigInt, 100000000) * Year(@d)
        + Month(@d) * 1000000
        + Day(@d) * 10000
        + DatePart(Hour, @d) * 100
        + DatePart(Minute, @d)

The returned data type here is a BigInt.

Answer 3

Using DATEPART:

SELECT CAST(DATEPART(yyyy, x.dt) AS VARCHAR(4)) + 
       CASE WHEN DATEPART(mm, x.dt) < 10 THEN  0 + CAST(DATEPART(mm, x.dt) AS VARCHAR(1)) ELSE CAST(DATEPART(mm, x.dt) AS VARCHAR(2)) END +
       CASE WHEN DATEPART(dd, x.dt) < 10 THEN  0 + CAST(DATEPART(dd, x.dt) AS VARCHAR(1)) ELSE CAST(DATEPART(dd, x.dt) AS VARCHAR(2)) END +
       CASE WHEN DATEPART(hh, x.dt) < 10 THEN  0 + CAST(DATEPART(hh, x.dt) AS VARCHAR(1)) ELSE CAST(DATEPART(hh, x.dt) AS VARCHAR(2)) END +
       CASE WHEN DATEPART(mi, x.dt) < 10 THEN  0 + CAST(DATEPART(mi, x.dt) AS VARCHAR(1)) ELSE CAST(DATEPART(mi, x.dt) AS VARCHAR(2)) END 
  FROM (SELECT  2010-06-08 16:31:47.667  dt) x

For SQL Server 2005+, I d look at creating a CLR function for format a date -- the C# DateTime.ToString() supports providing a more normal means of formatting the date.

友情链接