#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    2

    Numbers to date time...


    Is there any way to have numbers become a datetime? here is my code



    declare @month as integer
    declare @day as integer --1 or 16
    declare @year as integer
    declare @datefrom as varchar
    set @month = 10
    set @day = 1
    set @year = 2012
    set @datefrom = @month+'/'+@day+'/'+@year

    select @datefrom

    Ouput should be : 10/01/2012


    but i get this error,

    Server: Msg 245, Level 16, State 1, Line 10
    Syntax error converting the varchar value '/' to a column of data type int.

    could somebody help with me please
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391
    Code:
    declare @datefrom as varchar(10)
    set @month = 10
    set @day = 1
    set @year = 2012
    
    set @datefrom = right('0' + cast(@month as varchar(2)),2) + '/' +
                    right('0' + cast(@day as varchar(2)),2) + '/' + 
                    right('000' + cast(@year as varchar(4)),4)
    Note that you should specify a maximum length for the @datefrom variable.

    You can also do

    Code:
    set @datefrom = convert(varchar(10),datefromparts(@year,@month,@day),101)
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    2
    Originally Posted by swampBoogie
    Code:
    declare @datefrom as varchar(10)
    set @month = 10
    set @day = 1
    set @year = 2012
    
    set @datefrom = right('0' + cast(@month as varchar(2)),2) + '/' +
                    right('0' + cast(@day as varchar(2)),2) + '/' + 
                    right('000' + cast(@year as varchar(4)),4)
    Note that you should specify a maximum length for the @datefrom variable.

    You can also do

    Code:
    set @datefrom = convert(varchar(10),datefromparts(@year,@month,@day),101)
    could i know the complete code? it seems like i'm missing the function datefromparts? have you declared it as a seperate variable? or is it a UDF(User Defined Function)?

  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391
    Datefromparts is a built in function in SQL server. At least it was included in the 2008 release
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    2
    Originally Posted by swampBoogie
    Datefromparts is a built in function in SQL server. At least it was included in the 2008 release
    That sir, is what i'm lacking, i'm currently using SQL Server 2000.. is there any way to do this at my current instance?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391
    The first example I posted shall work in SQL Server 2000
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    101
    Rep Power
    9
    SELECT CONVERT(VARCHAR(10), GETDATE(), 101)
    should give you date in mm/dd/yyyy format
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    2
    Originally Posted by swampBoogie
    The first example I posted shall work in SQL Server 2000
    That sir, is what i need thanks sir!

IMN logo majestic logo threadwatch logo seochat tools logo