saving a date to a varchar field as YYYY-MM-DD

Question

Post reply

saving a date to a varchar field as YYYY-MM-DD

dave-dj

SSChampion

Points: 11297
More actions
September 9, 2010 at 9:38 am

#157193

Hi,
this sounds simple, but I can't seem to get it to work.!
I have a store procedure that basically generates a date, that needs to be put into a database table field, that has been define as: nvarchar(255)
I want the date to be saved as 'yyyy-mm-dd hh:mm:ss'
My stored procedure generates the converting the date time format
convert(datetime, @LogicalDate, 102)
this display as a query result of: 2010-09-09 00:00:00.000
yet, when it updates the table, it saves it as Sep 9 2010 12:00AM
Anyone got any idea how I get this saved in the format I want?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Viewing 15 posts - 1 through 14 (of 14 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Is @logicaldate defined as a datetime? If so, shouldn't the convert be:

convert(nvarchar(256), @LogicalDate, 102)

And then again, I might be wrong ...
David Webb

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

dave-dj (9/9/2010)
Hi,
this sounds simple, but I can't seem to get it to work.!
I have a store procedure that basically generates a date, that needs to be put into a database table field, that has been define as: nvarchar(255)
I want the date to be saved as 'yyyy-mm-dd hh:mm:ss'
My stored procedure generates the converting the date time format
convert(datetime, @LogicalDate, 102)
this display as a query result of: 2010-09-09 00:00:00.000
yet, when it updates the table, it saves it as Sep 9 2010 12:00AM
Anyone got any idea how I get this saved in the format I want?

Why are you converting your date to a datetime if you want to store it as a nvarchar? Convert it to nvarchar instead.

Try CONVERT(NVARCHAR(255), @LogicalDate, 120)

Edit: oops, looks like someone was faster than at posting an answer.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Assuming @logicaldate is datetime, it should be

SELECT CONVERT(NVARCHAR(255), @logicaldate, 102)

-Edit-

Lol, think I was a little slow :hehe:

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

3 of us posted an answer, but I think I'm the only one with the right format. (120 vs 102)

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

-- the stored procedure returns a DATETIME data type:

DECLARE @MyDate nvarchar(255)

SET @MyDate = GETDATE()

SELECT @MyDate

-- 'Sep 9 2010 4:46PM'

-- You need to CONVERT the output to a character type, CHAR(19) is perfect.

SELECT CONVERT(CHAR(19),GETDATE(),120)

-- '2010-09-09 16:45:02'

Heh yep Alvin, it's 120 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

Alvin Ramard (9/9/2010)
3 of us posted an answer, but I think I'm the only one with the right format. (120 vs 102)

True. I didn't really look at the format required, just spotted the error and used the same as the OP used.

So basically, I was slowest and didn't even read the whole question. . . having a good day 😉

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

skcadavre (9/9/2010)
Alvin Ramard (9/9/2010)
3 of us posted an answer, but I think I'm the only one with the right format. (120 vs 102)
True. I didn't really look at the format required, just spotted the error and used the same as the OP used.
So basically, I was slowest and didn't even read the whole question. . . having a good day 😉

At least you tried. That's what matter.

Have a good day too.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

dave-dj SSChampion Points: 11297 More actions · Answer 8

wow. Thanks for all the quick responses!. That's perfect thank you.

As always the ladies and gents of sqlserver central have been a great help.!

Everyday is a school day...............:-D

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 9

dave-dj (9/9/2010)
wow. Thanks for all the quick responses!. That's perfect thank you.
As always the ladies and gents of sqlserver central have been a great help.!
Everyday is a school day...............:-D

Let the schooling continue, then. 🙂 Storing dates and times as varchar or nvarchar values is one of the worst things you can do in SQL Server or any database for that matter. So, let me ask, why do you need to convert a date to an NVARCHAR?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 10

Jeff Moden (9/9/2010)
dave-dj (9/9/2010)
wow. Thanks for all the quick responses!. That's perfect thank you.
As always the ladies and gents of sqlserver central have been a great help.!
Everyday is a school day...............:-D
Let the schooling continue, then. 🙂 Storing dates and times as varchar or nvarchar values is one of the worst things you can do in SQL Server or any database for that matter. So, let me ask, why do you need to convert a date to an NVARCHAR?

Jeff, it's not just NVARCHAR. It's NVARCHAR(255). :w00t:

Based on the way the question was worded, I assumed the OP "inherited" the datatype.

After rereading the original question, I have another question. Why are we answering a question like this for someone with "MCITP: Business Intelligence Developer (2005)" in his signature?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Alvin Ramard (9/9/2010)
Jeff Moden (9/9/2010)
dave-dj (9/9/2010)
wow. Thanks for all the quick responses!. That's perfect thank you.
As always the ladies and gents of sqlserver central have been a great help.!
Everyday is a school day...............:-D
Let the schooling continue, then. 🙂 Storing dates and times as varchar or nvarchar values is one of the worst things you can do in SQL Server or any database for that matter. So, let me ask, why do you need to convert a date to an NVARCHAR?
Jeff, it's not just NVARCHAR. It's NVARCHAR(255). :w00t:
Based on the way the question was worded, I assumed the OP "inherited" the datatype.
After rereading the original question, I have another question. Why are we answering a question like this for someone with "MCITP: Business Intelligence Developer (2005)" in his signature?

CHAR(19) is perfect

I always hope that little hints like this will be picked up, and even if they're not used on the current query then maybe, just maybe, they'll be considered for the next one.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 12

Alvin Ramard (9/9/2010)
Jeff Moden (9/9/2010)
dave-dj (9/9/2010)
wow. Thanks for all the quick responses!. That's perfect thank you.
As always the ladies and gents of sqlserver central have been a great help.!
Everyday is a school day...............:-D
Let the schooling continue, then. 🙂 Storing dates and times as varchar or nvarchar values is one of the worst things you can do in SQL Server or any database for that matter. So, let me ask, why do you need to convert a date to an NVARCHAR?
Jeff, it's not just NVARCHAR. It's NVARCHAR(255). :w00t:
Based on the way the question was worded, I assumed the OP "inherited" the datatype.

Maybe but I'd love to hear from the OP. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 13

Chris Morris-439714 (9/10/2010)
Alvin Ramard (9/9/2010)
Jeff Moden (9/9/2010)
dave-dj (9/9/2010)
wow. Thanks for all the quick responses!. That's perfect thank you.
As always the ladies and gents of sqlserver central have been a great help.!
Everyday is a school day...............:-D
Let the schooling continue, then. 🙂 Storing dates and times as varchar or nvarchar values is one of the worst things you can do in SQL Server or any database for that matter. So, let me ask, why do you need to convert a date to an NVARCHAR?
Jeff, it's not just NVARCHAR. It's NVARCHAR(255). :w00t:
Based on the way the question was worded, I assumed the OP "inherited" the datatype.
After rereading the original question, I have another question. Why are we answering a question like this for someone with "MCITP: Business Intelligence Developer (2005)" in his signature?
CHAR(19) is perfect
I always hope that little hints like this will be picked up, and even if they're not used on the current query then maybe, just maybe, they'll be considered for the next one.

Ah, but we still don't know why the OP wants to make what's usually considered to be a mistake. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 14

Ok everyone... please let the OP answer for himself...

Dave,

It's normally a serious mistake when someone wants to convert a DATETIME to any type of character based output... why do you really need to do this? There may be high performance alternative if we knew.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)