Date calculations 2

[Gaylord]

Hi,

I am having a few problems with the formatting of dates using a SQL trigger.... I get todays date, no problem, then I add a day to it, try and format it and it comes across in a totally different format, for example...

I want the format to be 12/12/2004 and it gives me Dec 12 2004 12:00am

The code I have currently used is...

dateadd(d, 1, convert(datetime, @outputdate, 103))

Which works ok, but I just can't get it to go into my database (column is varchar)

Hope someone can help me as I feel like I am knocking my head against a wall!

Thanks

Jamie

 

[JamesLean]

Firstly, why aren't you using a datetime column to hold this date data?

If you really want to store it as character data in dd/mm/yyyy format then use:

CONVERT(varchar, GETDATE() + 1, 103)

(Use style 101 for mm/dd/yyyy format).

--James

 

[SQLBill]

First of all, the style portion of CONVERT only works if you are converting to a string (char,varchar,nchar,nvarchar), but not if you are converting to DATETIME. DATETIME is not stored in any specific format. It's an 8-byte value (see the BOL for more information). When you display a DATETIME value, it displays it using the COLLATION you set SQL Server to use. UNLESS you convert it to a string and provide the style information.

So, you say you are trying to store the DATETIME as a VARCHAR. Why? You should always store DATETIME as DATETIME. Why? 1. you can't do date calculations on strings. 2. You can't sort dates correctly when they are strings.

But if you really have to store it as VARCHAR...

[CODE}
CONVERT(VARCHAR(7),(dateadd(d, 1, convert(datetime, @outputdate))), 103)
[/CODE}

That will store JUST the date in the format dd/mm/yyyy. If you want the time also, you will need to increase the value for VARCHAR.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[r937]

First of all, the style portion of CONVERT only works if you are converting to a string (char,varchar,nchar,nvarchar), but not if you are converting to DATETIME

i used to think so too

but in fact it does work in both directions



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[SQLBill]

r937,

Okay, but how can it when the DATETIME is not stored in a date/time format? You can tell SQL Server to convert it to style 'whatever' but it ends up being stored as an 8-byte value with no format. So, if you convert it to 101 or 103 it still ends up being stored the exact same way.

The BOL even says in the style paragraph for CONVERT, that style is for conversions from DATETIME or SMALLDATETIME to character data. It doesn't say anything about the reverse.

-SQLBill

 

[r937]

That's what I used to think as well, but I believe the point was made and demonstrated on this forum a few months back that the convert function will use the style value when deciding whether to interpret "01/02/03" as "Jan 2, 2003", "Feb 1, 2003", or "Feb 3, 2001".

select convert(datetime, '01/02/03', 1)
select convert(datetime, '01/02/03', 3)
select convert(datetime, '01/02/03', 11)

Results:
2003-01-02 00:00:00.000
2003-02-01 00:00:00.000
2001-02-03 00:00:00.000

http://www.dbforums.com/showthread.php?postid=3605017#post3605017

but i don't have the previous url bookmarked

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[donutman]

Good tip rudy.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLBill]

Thank you Rudy. I learned something new....just like the BOL to not have it right.

-SQLBill

 

[ESquared]

Yeah, that was good. Thanks, Rudy!

As SQLBill said, BOL is deficient in covering this.

In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

Nothing about conversion from character to datetime. Annoying!

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)