Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Set Variable based on a SELECT Expand / Collapse
Author
Message
Posted Wednesday, February 10, 2010 8:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 10:48 PM
Points: 205, Visits: 1,341
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?
Post #863301
Posted Wednesday, February 10, 2010 8:38 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, December 16, 2013 2:06 AM
Points: 603, Visits: 260
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
Post #863309
Posted Wednesday, February 10, 2010 8:40 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
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.

Post #863311
Posted Wednesday, February 10, 2010 8:42 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
Hmmmm. Must type faster.

Converting oxygen into carbon dioxide, since 1955.

Post #863314
Posted Wednesday, February 10, 2010 8:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 10:48 PM
Points: 205, Visits: 1,341
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?
Post #863319
Posted Wednesday, February 10, 2010 9:08 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
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.

Post #863359
Posted Wednesday, February 10, 2010 9:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 10:48 PM
Points: 205, Visits: 1,341
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.
Post #863371
Posted Wednesday, February 10, 2010 9:15 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
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.

Post #863372
Posted Wednesday, February 10, 2010 9:18 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
OK, it does work. Must test better

Converting oxygen into carbon dioxide, since 1955.

Post #863375
Posted Wednesday, February 10, 2010 9:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 10:48 PM
Points: 205, Visits: 1,341
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?

Post #863385
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse