returning date with two digit year

[middletree]

I have a field that is stored in SQL Server in date/time format. On one
page, I only need the date to display. I am able to do this by saying:

DateValue(strLastModified)

However, this returns to me the date in this format:

05/10/2004

Instead, I would like to see:

05/10/04, but I am not sure how to do this.

 

[Aaron Bertrand [SQL Server MVP]]

05/10/2004

Instead, I would like to see:

05/10/04, but I am not sure how to do this.

Can I ask why you would like either of these formats? Both are ambiguous,
and the one you'd prefer is more so than the one you're currently using.

05/10/2004 - is that May 10th, or October 5th? Depends on what country you
are in/from...

05/10/04 - this could be just about anything, May 10th 2004, October 5th
2004, October 4th 2005...

Anyway, of course it is trivial to create your own functions that format the
date however you want it. Your specific format isn't offered, but it should
take about four seconds to adapt one of the samples:

http://www.aspfaq.com/2313

Personally, I try to stick with YYYY-MM-DD for all display values, and
YYYYMMDD for all values that are passed to a database.

A

 

[middletree]

Good questions. I am following the orders of my boss, and this is an
Intranet-based app, so only certain people will see it. There's one page
that the boss wants me to squeeze several columns on, yet he still wants it
narrow enough to fit onto the screen without scrolling.

I should add that before I post a question here, I do look elsewhere,
including your site and Microsoft, plus my MSDN library. But I didn't find
any pre-made function. I figured it fhter was one, I'd hear about it here,
and if not, I'd fiddle with the Right function.

 

[middletree]

Your specific format isn't offered, but it should
take about four seconds to adapt one of the samples:

http://www.aspfaq.com/2313

Hmm. I saw this article the first time around, but didn't really understand
how to use the sample code. For the format I want, I found this:

<%
response.write pd(DAY(date()),2) & "-" & _
pd(MONTH(date()),2) & "-" & _
pd(RIGHT(YEAR(date()),2),2)
%>

Of course, just need to replace the hyphen with a slash. But I don't really
get how to use this code. Does it require the code snippet at the top of the
page?

 

[Dave Anderson]

I have a field that is stored in SQL Server in date/time format. On
one page, I only need the date to display. I am able to do this by
saying:

DateValue(strLastModified)

However, this returns to me the date in this format:

05/10/2004

Instead, I would like to see:

05/10/04, but I am not sure how to do this.

SELECT RecordID,
CONVERT(CHAR(8),EffectiveDate,1) AS EffectiveDate,
...
FROM MyTable

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Evertjan.]

middletree wrote on 08 jul 2005 in microsoft.public.inetserver.asp.general:

this returns to me the date in this format:

05/10/2004

Instead, I would like to see:

05/10/04, but I am not sure how to do this.

The old straightforward basic way:

d = "05/10/2004"

d2 = left(d,6) & right(d,2)

 

[middletree]

Oh wow. That is pretty basic. I never even thought of that. Thanks!

 

[Aaron Bertrand [SQL Server MVP]]

But *WHY* would you do that when SQL already has the ability to give you

the date in the format you desire?

Because many would argue that string formatting is the job of the
client/presentation tier, not the database.

 

[Evertjan.]

Dave Anderson wrote on 11 jul 2005 in
microsoft.public.inetserver.asp.general:

But *WHY* would you do that when SQL already has the ability to give
you the date in the format you desire?

Because, Dave, one should have alternatives to choose from.

 

[middletree]

I could see both sides, but ultimately, I see it the way Aaron put it. The
presentation layer is the presentation layer.

Having said that, you provided me with a bit of SQL code that I had not been
aware of, so I thank you. I am sure it will come in handy down the road.

 

[middletree]

SELECT RecordID,
CONVERT(CHAR(8),EffectiveDate,1) AS EffectiveDate,
...
FROM MyTable

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp

I actually tried this and got an error; I'm reading through the doc you
linked me to, but thought I'd run it by you:

Here's the query, using response.write:

SELECT T.LastModified CONVERT(CHAR(8),EffectiveDate,1) AS EffectiveDate FROM
TKT_TICKET T WHERE TKT_TicketID = 16056

Note that LastModified is a time/date field, so the value it is starting
with is 2004-09-16 11:37:14.000



Here's the error, which I got both in the browser and then in the Query
Analyzer:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.

 

[middletree]

Actually, this is not working I think it's because some dates have a 0 in
front of them and some don't. I don't know why this is, but I'll have to
track it down.

 

[Aaron Bertrand [SQL Server MVP]]

SELECT T.LastModified CONVERT(CHAR(8),EffectiveDate,1) AS EffectiveDate

Not sure how this is Dave's fault. You need commas between
column/expression names. Would you say:

SELECT T.LastModified T.AnotherColumn T.YetAnotherColumn FROM Table T

Or should it be

SELECT T.LastModified, T.AnotherColumn, T.YetAnotherColumn FROM Table T

Also, if you ned the alias T on LastModified, why not also in the
EffectiveDate in the expression? Here's how I would write it, using
carriage returns between column names helps to identify these issues as
well.

SELECT
T.LastModified,
EffectiveDate = CONVERT(CHAR(8),EffectiveDate,1)
FROM
...

And finally, USE A STORED PROCEDURE.

 

[Aaron Bertrand [SQL Server MVP]]

SELECT

T.LastModified,
EffectiveDate = CONVERT(CHAR(8),EffectiveDate,1)
FROM

Sorry,

SELECT
T.LastModified,
EffectiveDate = CONVERT(CHAR(8),T.EffectiveDate,1)
FROM

(Or, if you are only selecting from one table, drop the alias altogether.)

 

[middletree]

I didn't mean to say it was his fault.

Here's what worked:
SELECT CONVERT(CHAR(8),T.LastModified,1) AS EffectiveDate FROM TKT_TICKET T
WHERE T.TKT_TicketID = 16056

 

[Evertjan.]

middletree wrote on 11 jul 2005 in

in message

Actually, this is not working I think it's because some dates have a 0
in front of them and some don't. I don't know why this is, but I'll
have to track it down.

[Please do not toppost on usenet]

Re: oldfasioned Basic

I only supplied code as per your specifications 05/10/04

If the leading zeros could be missing, try:

d = "05/10/2004"
a = split(d,"/")
d2 = a(0) & "/" & a(1) & "/" & right(a(2),2)

and if you want to reinstate those leading zeros:

function two(x)
two = right("0" & x,2)
end function

d = "05/10/2004"
a = split(d,"/")
d2 = two(a(0)) & "/" & two(a(1)) & "/" & two(a(2))

Programming is a game, an art, and a way to joke.

 

[Aaron Bertrand [SQL Server MVP]]

Why do you have this desire to force everyone to post the way you do? Do
you like scrolling through irrelevant and redundant text so much that you
think everyone on "usenet" should have to do it?

 

[Evertjan.]

Aaron Bertrand [SQL Server MVP] wrote on 11 jul 2005 in
microsoft.public.inetserver.asp.general:

Why do you have this desire to force everyone to post the way you do?
Do you like scrolling through irrelevant and redundant text so much
that you think everyone on "usenet" should have to do it?

[Please do not toppost on usenet]

Next to the many excellent ideas you have, Aaron,
you have a strange idea of the word "force".

Usually, when I force someone, I don't say "please".

 

[Aaron Bertrand [SQL Server MVP]]

Okay. Please stop asking everyone and their brother to post the way you
want them to. And please stop including irrelevant text that requires more
scrolling if you are going to continue bottom-posting.

 

[middletree]

I never know what format to use. I hate bottom-posting, and most people I
know agree with me. But whatever the local rules are, I'll try and follow
'em. At any rate, thanks for the amended code.