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

getdate() in dynamic SQL Expand / Collapse
Author
Message
Posted Monday, May 26, 2008 4:13 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 25, 2013 5:36 PM
Points: 267, Visits: 126
I would like to use the value of GetDate() within a dynamic SQL statement.
THe following line:

', DateUpdated = '+ GetDate()+ CHAR(13)+

produces a syntax error "error converting datetime from character string"

Thanks for the help.

Sam



Post #506592
Posted Monday, May 26, 2008 5:27 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:54 AM
Points: 9,294, Visits: 9,492
Try this:
', DateUpdated = '+ CAST(GetDate() as varchar(24))+ CHAR(13)+  



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #506610
Posted Monday, May 26, 2008 5:28 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:54 AM
Points: 9,294, Visits: 9,492
Though looking at it I think that you will need more apostrophes...

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #506611
Posted Tuesday, May 27, 2008 2:17 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:47 AM
Points: 967, Visits: 450
smknox (5/26/2008)
I would like to use the value of GetDate() within a dynamic SQL statement.
THe following line:

', DateUpdated = '+ GetDate()+ CHAR(13)+

produces a syntax error "error converting datetime from character string"

Thanks for the help.

Sam

Try This

', DateUpdated = '''+ CONVERT(VARCHAR(12),GETDATE(),101)+''''+ CHAR(13) +


Prasad Bhogadi
www.inforaise.com
Post #506746
Posted Tuesday, May 27, 2008 2:21 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:47 AM
Points: 967, Visits: 450
You may want to use the convert function based on how you want to save the output of GETDATE() function in your DateUpdated Column.


Prasad Bhogadi
www.inforaise.com
Post #506747
Posted Tuesday, May 27, 2008 4:28 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
', DateUpdated = GetDate() ' + CHAR(13)
:)
Post #506794
Posted Tuesday, May 27, 2008 4:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:47 AM
Points: 967, Visits: 450
Sergiy (5/27/2008)
', DateUpdated = GetDate() ' + CHAR(13)
:)

This is straight forward. We were trying to re-invent the wheel :)



Prasad Bhogadi
www.inforaise.com
Post #506799
Posted Tuesday, May 27, 2008 6:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 25, 2013 5:36 PM
Points: 267, Visits: 126
Thanks! Sergei's syntax was it!

Sam



Post #506874
Posted Thursday, June 12, 2008 10:30 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 6:13 PM
Points: 318, Visits: 1,183
One very small thing: unless you're going to be somehow running the dynamic SQL string on a pre-OSX Mac(!) I'd use the "correct" Windows CRLF sequence, which is "char(13) + char(10)" rather than just plain "char(13)".

Doesn't make a difference to the SQL parser, but it's cleaner if you're going to be visualising the string in most Windows apps.

Regards,

Jacob
Post #516401
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse