datetime conversion challenge

Question

Post reply

datetime conversion challenge

PeterR-1037872

SSC Veteran

Points: 282
More actions
August 15, 2012 at 11:22 pm

#265100

How do I convert this date '19/10/2011 2:00:00 PM' to a standard datetime value?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

vijayarani87.s SSC Eights! Points: 890 More actions · Answer 1

SELECT CONVERT(VARCHAR(10), '2012-08-17 00:00:00.000', 111) AS [YYYY/MM/DD]

vijayarani87.s SSC Eights! Points: 890 More actions · Answer 2

SELECT convert(datetime, '10/19/2011 2:00:00 PM') -- mm/dd/yyyy

BrainDonor SSCoach Points: 19254 More actions · Answer 3

Although a bit dated (no pun intended) there is an interesting article by Robyn Page for various date and time conversions - http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/.

Steve Hall
Linkedin
Blog Site

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

BrainDonor (8/16/2012)
Although a bit dated (no pun intended) there is an interesting article by Robyn Page for various date and time conversions - http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/.

Very good it is too.

SELECT

DateFromString,

DateBackToString = CONVERT(VARCHAR(23),DateFromString,103) -- drops time component

FROM (

SELECT DateFromString = CONVERT(DATETIME,'19/10/2011 2:00:00 PM',103) -- uses time component

) d

^{“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

PeterR-1037872 SSC Veteran Points: 282 More actions · Answer 5

Thanks vijayarani87.s

This simple solution worked perfectly - I just had to include 'set dateformat dmy' before the 'convert'.

Much appreciated,

Peter