Post reply

Set Variable based on a SELECT

  • February 10, 2010 at 8:30 am

    #138827

    I'm still Googling and searching SSC for my answer, but here is my problem.

    I need to set a VARIABLE based on the result of a SELECT

    SELECT CONVERT(CHAR(9),GETDATE(),1) + SUBSTRING(CONVERT(CHAR(5),GETDATE(),14),1,5) + ' '

    which results in...

    02/10/10 07:26

    This is my code

    DECLARE @mydate DATETIME

    SET @myDate = CONVERT(CHAR(9),GETDATE(),1) + SUBSTRING(CONVERT(CHAR(5),GETDATE(),14),1,5) + ' '

    print @myDate

    which results in...

    Feb 10 2010 7:28AM

    What am I doing wrong?

  • February 10, 2010 at 8:38 am

    #1116880

    Hi,

    since @mydate is of type DATETIME you get an implicit conversion of your neatly formatted string back to datetime.

    Declare @mydate as a CHAR(14), or what you find suitable, and everything should work as expected...

    /Markus

  • February 10, 2010 at 8:40 am

    #1116882

    DECLARE @mydate varchar(20)

    SELECT @myDate = CONVERT(CHAR(9),GETDATE(),1) + SUBSTRING(CONVERT(CHAR(5),GETDATE(),14),1,5) + ' '

    print @mydate

    Converting oxygen into carbon dioxide, since 1955.

  • February 10, 2010 at 8:42 am

    #1116885

    Hmmmm. Must type faster.:-)

    Converting oxygen into carbon dioxide, since 1955.

  • February 10, 2010 at 8:44 am

    #1116889

    Thanks Markus and Steve! You guyes are great! That works!

    But since @mydate is declared as varchar(20), can I still INSERT it into a TABLE's field that is defined as DATETIME? How does that work?

  • February 10, 2010 at 9:08 am

    #1116923

    You can convert it back to datetime if you need to :

    INSERT INTO tableA (columnA)

    SELECT CONVERT(datetime,@mydate)

    Converting oxygen into carbon dioxide, since 1955.

  • February 10, 2010 at 9:15 am

    #1116930

    Thanks, Steve.

    I tested INSERTs with both VARCHAR & your DATETIME convertion into my DATETIME field and the results are the same. So I guess it doesn't matter.

  • February 10, 2010 at 9:15 am

    #1116931

    Well that won't work. In this case I would use two variables based off of the same value such as :

    DECLARE @mydate datetime, @mydatestring varchar(20)

    SELECT @mydate = getdate()

    SELECT @mydatestring = CONVERT(CHAR(9),@mydate,1) + SUBSTRING(CONVERT(CHAR(5),@mydate,14),1,5) + ' '

    PRINT @mydatestring

    INSERT INTO TableA (columnA)

    SELECT @mydate

    Converting oxygen into carbon dioxide, since 1955.

  • February 10, 2010 at 9:18 am

    #1116933

    OK, it does work. Must test better 🙂

    Converting oxygen into carbon dioxide, since 1955.

  • February 10, 2010 at 9:27 am

    #1116943

    1) Then when I do a SELECT on the DATETIME field in the TABLE, I get this:

    2010-02-10 08:12:00.000

    2) But when I use SSMS and do an OPEN TABLE to view the data, I get this:

    2/10/2010 8:12:00 AM

    When I do a SELECT on the data I want to "see" option 2 (2/10/2010 8:12:00 AM).

    So I assume I have to format my SELECT in order to CONVERT the value into the FORMAT I want to see?

  • February 10, 2010 at 10:41 am

    #1117008

    That's pretty much it. You store and manipulate in datetime format and output/format to a string, usually.

    Converting oxygen into carbon dioxide, since 1955.

  • February 10, 2010 at 10:43 am

    #1117010

    Thank you, Steve.

Viewing 12 posts - 1 through 11 (of 11 total)

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