how to convert datepart values to varchar

[ije]

i need to insert into a varchar field the current date, but in yyyy-mm-dd format. I thought I could do it using

datepart(yyyy,getdate()) & '-' & datepart(mm,getdate()) & '-' & datepart(dd,getdate())

or even

datepart(yyyy,getdate()) + '-' + datepart(mm,getdate()) + '-' + datepart(dd,getdate())

The first example returns 0 and the seconds adds up the values.

Can someone help?

 

[clarkin]

"..insert into.."

Are you inserting into a database? If so you can probably do it there much more easily

Otherwise check out the DatePart() function in BOL.

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[ije]

I am inserting into SQL db - not sure what you mean by it being easier to do it there? I can isolate the date parts OK, just cant return it to the text field with dashes between the elements returned....

 

[clarkin]

Well, run this in SQL Server:

SELECT CONVERT(char(10),GETDATE(),120)

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[ije]

excellent!! that worked great! thanks a lot.

As you know this so well, maybe you can assist a little more....in some fields I need to put the date forward, say 10 days to act as a target date? Is there a way to do this, taking into effect times when the additional 10 days will also take it into a new month?

Likewise, I need to insert a time value hh:mm:ss into a text field?

 

[clarkin]

tbh, you might be better off having a datetime field then

But yes, have a look at the T-SQL reference in Books OnLine.
DATEADD() and CAST or CONVERT() will sort you out.

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].