converting to datetime data type

• sahana

  SSCrazy

  Points: 2530

  More actions

  May 27, 2005 at 9:42 am

  #76321

  year=2005

  Month=5

  day=27

  time=9:30

  I have these values with me and I wanted to convert this to a datetime field.

  SELECT CAST(year AS varchar)+'-'+CAST(month AS varchar)+'-'+CAST(day AS varchar)+' '+CAST(time as varchar)+':'+'00'

   FROM tab

  I am trying to convert this into datetime by using the above quesry.

  But I wanted to convert month 5 as as 05 and time 9:30 as 09:30.

  And also If i get day as 2 I need to convert it to 02.

  Any help would be greatly appreciated.

  Thanks.

• Lowell

  SSC Guru

  Points: 323495

  More actions

  May 27, 2005 at 10:10 am

  #561546

  select CONVERT ( datetime,getdate(),102 ) might do what you want:

  2005-05-27 11:55:35.467

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  May 27, 2005 at 10:10 am

  #561545

  This may not achieve what you want, but I am guessing this is for display reasons. 

  SELECT CONVERT( varchar, CONVERT( datetime, '1/1/2005 09:00:00 AM'), 101) + ' ' +  CONVERT( varchar, CONVERT( datetime, '1/1/2005 09:00:00 AM'), 108)

  I wasn't born stupid - I had to study.

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  May 27, 2005 at 10:34 am

  #561551

  I tried that too Lowell, but my setting in Query Analyzer yielded:  5/27/2005 12:18:47 PM. 

  That is why I suggested a more, (shall we say) convoluted answer. 

  I wasn't born stupid - I had to study.

• ron k

  SSCertifiable

  Points: 6053

  More actions

  May 27, 2005 at 12:57 pm

  #561573

  This should give you a hint:

  select Right('00' + cast(month(getdate()) as varchar),2)

  ----

  05

• Nicolas DONADIO MANOLIDIS

  Ten Centuries

  Points: 1118

  More actions

  May 27, 2005 at 12:59 pm

  #561574

  Farrell... there is a 2 steps solution... i think this will work fine... at least it worked with my configuration...

  But I suggest that you take a look at BOL, under topic CAST & CONVERT... there is a table comparing the diferent kinds of convertions that you can perform using cast and covert functions.

  declare @date datetime

  SELECT 

       @Date = convert(datetime, (year+'-'+month+'-'+day+' '+time+':00'), 102)

  FROM YourTable

  SELECT convert(varchar(20),@date,110)+' '+convert(varchar(20),@date,108)

   

  Nicolas Donadio

  Sr. Software Developer

  DPS Automation

  ARGENTINA

   

   

   

   

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  May 27, 2005 at 1:08 pm

  #561576

  Thanks Nicolas.  I understand those.  I misread the initial question and thought this was a date and not seperate fields for each part of a date.  That is why I suggested the concatenation using a specific date so the leading zero's would display.  ooops... 

  I wasn't born stupid - I had to study.

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  May 30, 2005 at 2:02 am

  #561661

  Another approach might be:

  SELECT

    CAST(

     CAST(

      (@year * 10000 +

      @month * 100 +

      @day)

     AS CHAR(8))+' '+ @time

    AS DATETIME)

   

  Note, that this doesn't check for a valid date before trying to CAST to DATETIME. If you also need to do this, see if this helps: http://www.sql-server-performance.com/fk_datetime.asp

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]