Convert Date

• craig.jenkins

  SSCommitted

  Points: 1643

  More actions

  September 21, 2018 at 6:53 am

  #408483

  Hi Guys,
  Is there a way to convert the Datein in the below to show this format MMM DD YYYY

  Thanks

  SELECT DateIn
  ,Booking
  ,RegNo
  ,CustName
  ,Model
  ,ServText
  ,Phone
  ,Team
  ,Facilities
  --,TimeIn
  --,TimeOut
  , CONCAT(TIMEIN, '-' , TIMEOUT) as [In-Out]
  ,TimeOut - TimeIn AS Hrs
  --,DateReg
  FROM Bookings
  WHERE DATEIN >= GETDATE()+6 AND DATEIN < GETDATE()+7
  AND DEALER = 'BA'
  --AND SERVTEXT NOT LIKE '%DIAG%'
  --AND Facilities LIKE '%WAIT%'
  --AND DateReg >= DATEADD(month, 12, getdate())
  ORDER BY DateReg

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  September 21, 2018 at 7:01 am

  #2006543

  What tool will actually run the query?   You don't have to do anything at all in your query if you use a reporting tool.   You can just format the value in the reporting tool.   However, if you have no such tool, you can use the FORMAT function.   Just be aware that it will slow down your query., and potentially seriously.   Look up the syntax online for it.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Jonathan AC Roberts

  SSCoach

  Points: 18271

  More actions

  September 21, 2018 at 7:06 am

  #2006544

  craig.jenkins - Friday, September 21, 2018 6:53 AM

  Hi Guys,
  Is there a way to convert the Datein in the below to show this format MMM DD YYYY

  Thanks

  SELECT DateIn
  ,Booking
  ,RegNo
  ,CustName
  ,Model
  ,ServText
  ,Phone
  ,Team
  ,Facilities
  --,TimeIn
  --,TimeOut
  , CONCAT(TIMEIN, '-' , TIMEOUT) as [In-Out]
  ,TimeOut - TimeIn AS Hrs
  --,DateReg
  FROM Bookings
  WHERE DATEIN >= GETDATE()+6 AND DATEIN < GETDATE()+7
  AND DEALER = 'BA'
  --AND SERVTEXT NOT LIKE '%DIAG%'
  --AND Facilities LIKE '%WAIT%'
  --AND DateReg >= DATEADD(month, 12, getdate())
  ORDER BY DateReg

  FORMAT(DateIn,N'MMM dd yyyy')

• below86

  SSChampion

  Points: 11516

  More actions

  September 21, 2018 at 7:50 am

  #2006559

  You could try this:
  CONVERT(CHAR(11), DateIn, 107) AS DateIn

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.

• Jonathan AC Roberts

  SSCoach

  Points: 18271

  More actions

  September 21, 2018 at 8:07 am

  #2006565

  below86 - Friday, September 21, 2018 7:50 AM

  You could try this:
  CONVERT(CHAR(11), DateIn, 107) AS DateIn

  That's nearly there but it also adds a comma e.g."Sep 21, 2018"

• craig.jenkins

  SSCommitted

  Points: 1643

  More actions

  September 21, 2018 at 8:18 am

  #2006573

  Thanks guys much appreciated FORMAT(DateIn,N'MMM dd yyyy') works a treat

• Jeff Moden

  SSC Guru

  Points: 1003899

  More actions

  September 21, 2018 at 8:41 am

  #2006581

  Jonathan AC Roberts - Friday, September 21, 2018 7:06 AM

  craig.jenkins - Friday, September 21, 2018 6:53 AM

  Hi Guys,
  Is there a way to convert the Datein in the below to show this format MMM DD YYYY

  Thanks

  SELECT DateIn
  ,Booking
  ,RegNo
  ,CustName
  ,Model
  ,ServText
  ,Phone
  ,Team
  ,Facilities
  --,TimeIn
  --,TimeOut
  , CONCAT(TIMEIN, '-' , TIMEOUT) as [In-Out]
  ,TimeOut - TimeIn AS Hrs
  --,DateReg
  FROM Bookings
  WHERE DATEIN >= GETDATE()+6 AND DATEIN < GETDATE()+7
  AND DEALER = 'BA'
  --AND SERVTEXT NOT LIKE '%DIAG%'
  --AND Facilities LIKE '%WAIT%'
  --AND DateReg >= DATEADD(month, 12, getdate())
  ORDER BY DateReg

  FORMAT(DateIn,N'MMM dd yyyy')

  craig.jenkins - Friday, September 21, 2018 8:18 AM

  Thanks guys much appreciated FORMAT(DateIn,N'MMM dd yyyy') works a treat

  Just a suggestion.... avoid the use of FORMAT.  While it's incredibly easy to use, it's 44 times slower than CONVERT and many other methods.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1003899

  More actions

  September 21, 2018 at 8:47 am

  #2006582

  below86 - Friday, September 21, 2018 7:50 AM

  You could try this:
  CONVERT(CHAR(11), DateIn, 107) AS DateIn

  107 is going to have a comma in it.  I believe you meant 109.  100 would also work.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jonathan AC Roberts

  SSCoach

  Points: 18271

  More actions

  September 21, 2018 at 9:04 am

  #2006588

  Jeff Moden - Friday, September 21, 2018 8:41 AM

  Jonathan AC Roberts - Friday, September 21, 2018 7:06 AM

  craig.jenkins - Friday, September 21, 2018 6:53 AM

  Hi Guys,
  Is there a way to convert the Datein in the below to show this format MMM DD YYYY

  Thanks

  SELECT DateIn
  ,Booking
  ,RegNo
  ,CustName
  ,Model
  ,ServText
  ,Phone
  ,Team
  ,Facilities
  --,TimeIn
  --,TimeOut
  , CONCAT(TIMEIN, '-' , TIMEOUT) as [In-Out]
  ,TimeOut - TimeIn AS Hrs
  --,DateReg
  FROM Bookings
  WHERE DATEIN >= GETDATE()+6 AND DATEIN < GETDATE()+7
  AND DEALER = 'BA'
  --AND SERVTEXT NOT LIKE '%DIAG%'
  --AND Facilities LIKE '%WAIT%'
  --AND DateReg >= DATEADD(month, 12, getdate())
  ORDER BY DateReg

  FORMAT(DateIn,N'MMM dd yyyy')

  craig.jenkins - Friday, September 21, 2018 8:18 AM

  Thanks guys much appreciated FORMAT(DateIn,N'MMM dd yyyy') works a treat

  Just a suggestion.... avoid the use of FORMAT.  While it's incredibly easy to use, it's 44 times slower than CONVERT and many other methods.

  Yes, it's much slower I just did a 1 million row test:
  CONVERT(CHAR(10), datestamp, 100)
  CPU time = 515 ms, elapsed time = 516 ms.

  FORMAT(datestamp,N'MMM dd yyyy')
  CPU time = 13868 ms, elapsed time = 14928 ms.
  And 234 ms of both is taken with other parts of the query. So on my machine it's 53 times slower.

• Jeff Moden

  SSC Guru

  Points: 1003899

  More actions

  September 21, 2018 at 9:22 am

  #2006589

  Jonathan AC Roberts - Friday, September 21, 2018 9:04 AM

  Jeff Moden - Friday, September 21, 2018 8:41 AM

  Jonathan AC Roberts - Friday, September 21, 2018 7:06 AM

  craig.jenkins - Friday, September 21, 2018 6:53 AM

  Hi Guys,
  Is there a way to convert the Datein in the below to show this format MMM DD YYYY

  Thanks

  SELECT DateIn
  ,Booking
  ,RegNo
  ,CustName
  ,Model
  ,ServText
  ,Phone
  ,Team
  ,Facilities
  --,TimeIn
  --,TimeOut
  , CONCAT(TIMEIN, '-' , TIMEOUT) as [In-Out]
  ,TimeOut - TimeIn AS Hrs
  --,DateReg
  FROM Bookings
  WHERE DATEIN >= GETDATE()+6 AND DATEIN < GETDATE()+7
  AND DEALER = 'BA'
  --AND SERVTEXT NOT LIKE '%DIAG%'
  --AND Facilities LIKE '%WAIT%'
  --AND DateReg >= DATEADD(month, 12, getdate())
  ORDER BY DateReg

  FORMAT(DateIn,N'MMM dd yyyy')

  craig.jenkins - Friday, September 21, 2018 8:18 AM

  Thanks guys much appreciated FORMAT(DateIn,N'MMM dd yyyy') works a treat

  Just a suggestion.... avoid the use of FORMAT.  While it's incredibly easy to use, it's 44 times slower than CONVERT and many other methods.

  Yes, it's much slower I just did a 1 million row test:
  CONVERT(CHAR(10), datestamp, 100)
  CPU time = 515 ms, elapsed time = 516 ms.

  FORMAT(datestamp,N'MMM dd yyyy')
  CPU time = 13868 ms, elapsed time = 14928 ms.
  And 234 ms of both is taken with other parts of the query. So on my machine it's 53 times slower.

  Thanks for that.  I believe, however , the your first test needs a minor correction "MMM DD YYYY" occupies 11 characters and so the CHAR(10) should be CHAR(11).  It won't make much difference in the timing, though.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• jonas.gunnarsson 52434

  Ten Centuries

  Points: 1240

  More actions

  September 21, 2018 at 9:32 am

  #2006591

  You need char(11).
  select convert(char(11), getdate(), 100) as DefaultDateFormat;
  See https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 21, 2018 at 9:42 am

  #2006595

  craig.jenkins - Friday, September 21, 2018 8:18 AM

  Thanks guys much appreciated FORMAT(DateIn,N'MMM dd yyyy') works a treat

  If you find this to be too slow (I have heard that FORMAT is a dog), you could go to this: REPLACE(CONVERT(CHAR(11), DateIn, 107),',','') AS DateIn

• Jonathan AC Roberts

  SSCoach

  Points: 18271

  More actions

  September 21, 2018 at 9:51 am

  #2006599

  Jeff Moden - Friday, September 21, 2018 9:22 AM

  Jonathan AC Roberts - Friday, September 21, 2018 9:04 AM

  Jeff Moden - Friday, September 21, 2018 8:41 AM

  Jonathan AC Roberts - Friday, September 21, 2018 7:06 AM

  craig.jenkins - Friday, September 21, 2018 6:53 AM

  Hi Guys,
  Is there a way to convert the Datein in the below to show this format MMM DD YYYY

  Thanks

  SELECT DateIn
  ,Booking
  ,RegNo
  ,CustName
  ,Model
  ,ServText
  ,Phone
  ,Team
  ,Facilities
  --,TimeIn
  --,TimeOut
  , CONCAT(TIMEIN, '-' , TIMEOUT) as [In-Out]
  ,TimeOut - TimeIn AS Hrs
  --,DateReg
  FROM Bookings
  WHERE DATEIN >= GETDATE()+6 AND DATEIN < GETDATE()+7
  AND DEALER = 'BA'
  --AND SERVTEXT NOT LIKE '%DIAG%'
  --AND Facilities LIKE '%WAIT%'
  --AND DateReg >= DATEADD(month, 12, getdate())
  ORDER BY DateReg

  FORMAT(DateIn,N'MMM dd yyyy')

  craig.jenkins - Friday, September 21, 2018 8:18 AM

  Thanks guys much appreciated FORMAT(DateIn,N'MMM dd yyyy') works a treat

  Just a suggestion.... avoid the use of FORMAT.  While it's incredibly easy to use, it's 44 times slower than CONVERT and many other methods.

  Yes, it's much slower I just did a 1 million row test:
  CONVERT(CHAR(10), datestamp, 100)
  CPU time = 515 ms, elapsed time = 516 ms.

  FORMAT(datestamp,N'MMM dd yyyy')
  CPU time = 13868 ms, elapsed time = 14928 ms.
  And 234 ms of both is taken with other parts of the query. So on my machine it's 53 times slower.

  Thanks for that.  I believe, however , the your first test needs a minor correction "MMM DD YYYY" occupies 11 characters and so the CHAR(10) should be CHAR(11).  It won't make much difference in the timing, though.

  It didn't make much difference. Probably slowed it down a tiny bit so it's about 44 times slower now.😉

• Jeff Moden

  SSC Guru

  Points: 1003899

  More actions

  September 21, 2018 at 10:06 pm

  #2006643

  Lynn Pettis - Friday, September 21, 2018 9:42 AM

  craig.jenkins - Friday, September 21, 2018 8:18 AM

  Thanks guys much appreciated FORMAT(DateIn,N'MMM dd yyyy') works a treat

  If you find this to be too slow (I have heard that FORMAT is a dog), you could go to this: REPLACE(CONVERT(CHAR(11), DateIn, 107),',','') AS DateIn

  Or just CONVERT(CHAR(11),DateIn,100)

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• below86

  SSChampion

  Points: 11516

  More actions

  September 24, 2018 at 8:22 am

  #2006744

  Jeff Moden - Friday, September 21, 2018 10:06 PM

  Lynn Pettis - Friday, September 21, 2018 9:42 AM

  craig.jenkins - Friday, September 21, 2018 8:18 AM

  Thanks guys much appreciated FORMAT(DateIn,N'MMM dd yyyy') works a treat

  If you find this to be too slow (I have heard that FORMAT is a dog), you could go to this: REPLACE(CONVERT(CHAR(11), DateIn, 107),',','') AS DateIn

  Or just CONVERT(CHAR(11),DateIn,100)

  That would be better, didn't read the OP carefully enough, made a bad assumption that you would want the comma.  Thanks for the info on the FORMAT as well, didn't know that, but then I rarely ever need to use it.

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.