ansaurus

Question

Answer 1

+3 A:

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)

SQLMenace 2010-06-08 20:46:49

+1: Last one is the best IMO

OMG Ponies 2010-06-08 21:05:51

Thanks............

Muhammad Akhtar 2010-06-08 21:11:27

Answer 2

A:

got same solution as omgponie

Patrick Säuerl 2010-06-08 20:54:15

Answer 3

+1 A:

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.

OMG Ponies 2010-06-08 20:54:54

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

SQLMenace 2010-06-08 20:55:55

@SQLMenace: I missed it when I looked at the CAST/CONVERT documentation. Date formatting in TSQL is a PITA - the CLR option is more accommodating.

OMG Ponies 2010-06-08 21:05:05

Some shops don't allow CLR, either way this should be done client side unless he needs to bcp into a file in that case there is no choice

SQLMenace 2010-06-08 21:09:34

Here is also a way that will always show 2 digits, no need for case, select RIGHT('00' + CAST(DATEPART(mm, getdate()) AS VARCHAR(2)),2)

SQLMenace 2010-06-08 21:10:04

Answer 4

+2 A:

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.

G Mastros 2010-06-09 16:58:17

another good way, I am up voting, thanks for sharing

Muhammad Akhtar 2010-06-09 18:30:24

ansaurus

tags:

views:

answers:

Getting date string from getdate method

related questions