How to have DATE formate YYYY-MM-DD ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to have DATE formate YYYY-MM-DD ?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-24 : 06:40:04

Hi

I am having a procedure and try to get the date (current date)as 'launchdate' in the output. So how do I get the right date format as below?

YYYY-MM-DD - if this is difficult then how do I get the below format

2007-08-23T14:26:53-07:00

Advance thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 06:41:51

The built-in CONVERT function do this for you!
Have a look in Books Online immediately...

HINT: Option 126

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 06:44:37

The first question is solved with same CONVERT function.
Then you only need 10 characters to CONVERT to CHAR(10).

HINT: Option 120

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-24 : 06:51:36

Where do you want to show formatted dates?

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-24 : 07:40:26

Hi

I have used the below code

DECLARE @reldate as datetime
set @reldate =CAST(CONVERT(char(10), getdate(), 112) AS datetime)
@reldate as testdate

the result is this 2007-08-24 00:00:00.000

Any Idea where I am making mistakes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 08:23:34

It depends on what you expected...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 08:24:59

DATETIME's don't have a format!
All DATETIME's really are, is an offset to a certain point in time [history].

If you want the date FORMATTED as string, use

SELECT CONVERT(VARCHAR, CURRENT_TIMESTAMP, 127)

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-24 : 08:33:35

quote:
Originally posted by Vaishu

Hi

I have used the below code

DECLARE @reldate as datetime
set @reldate =CAST(CONVERT(char(10), getdate(), 112) AS datetime)
@reldate as testdate

the result is this 2007-08-24 00:00:00.000

Any Idea where I am making mistakes

Did you read my question?

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-24 : 09:06:27

Hi

I am excuting an sql procedure in the query analyser. so try to get today' date as YYYY-MM-DD but not with a timestamp. Output means the result in the query analyser.

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-24 : 09:10:27

quote:
Originally posted by Vaishu

Hi

I am excuting an sql procedure in the query analyser. so try to get today' date as YYYY-MM-DD but not with a timestamp. Output means the result in the query analyser. The format should be

YYYY-MM-DD no time stamp

or
2007-08-23T14:26:53-07:00
(Can u see the 'T' and last two set of times seperated by '-')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-24 : 09:13:39

quote:
Originally posted by Vaishu

Hi

I am excuting an sql procedure in the query analyser. so try to get today' date as YYYY-MM-DD but not with a timestamp. Output means the result in the query analyser.

If you dont use any front end, then try

select convert(varchar(10),getdate(),120)

But I dont understand what you are going to do with QA result

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 09:14:18

Since you are not able read proper, here it is again.

SELECT CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 120)

E 12°55'05.25"
N 56°04'39.16"

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-24 : 09:14:44

quote:
Originally posted by Peso

DATETIME's don't have a format!
All DATETIME's really are, is an offset to a certain point in time [history].

If you want the date FORMATTED as string, use

SELECT CONVERT(VARCHAR, CURRENT_TIMESTAMP, 127)

E 12°55'05.25"
N 56°04'39.16"

127 is invalid.

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-24 : 09:16:49

Hi peso

Thank you. It works

CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 127)-- 127 doesn't work, I used 120 or 126

quote:
Originally posted by Peso

DATETIME's don't have a format!
All DATETIME's really are, is an offset to a certain point in time [history].

If you want the date FORMATTED as string, use

SELECT CONVERT(VARCHAR, CURRENT_TIMESTAMP, 127)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 09:17:36

Since when?

This my result in SSMS
2007-08-24T15:16:59.177

E 12°55'05.25"
N 56°04'39.16"

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-24 : 09:19:28

quote:
Originally posted by Vaishu

Hi

I am having a procedure and try to get the date (current date)as 'launchdate' in the output. So how do I get the right date format as below?

YYYY-MM-DD - if this is difficult then how do I get the below format

2007-08-23T14:26:53-07:00

Advance thanks

What you need is something like this:

SELECT CAST(CONVERT(VARCHAR, GETDATE(), 120) AS CHAR(10))

To get YYYY-MM-DD, but if you make @reldate a datetime you will kill the formatting.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-24 : 09:22:13

quote:
Originally posted by Peso

Since when?

This my result in SSMS
2007-08-24T15:16:59.177

E 12°55'05.25"
N 56°04'39.16"

Fails in 2000, I didn't try in 2005.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-24 : 09:23:21

DECLARE @InDate datetime
DECLARE @OutDate varchar(10)
SET @InDate = GETDATE()
SET @OutDate = CONVERT(varchar(10),@InDate,120)
SELECT @OutDate

Jim

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-24 : 09:27:34

Hi Madhi

I am calling the sql procedure from asp.net, then generate csv file for bulk upload and import to the ACCPAC (Sage) and scheduled. I am not familer to sql procedure.

quote:
Originally posted by madhivanan

quote:
Originally posted by Vaishu

Hi

I am excuting an sql procedure in the query analyser. so try to get today' date as YYYY-MM-DD but not with a timestamp. Output means the result in the query analyser.

If you dont use any front end, then try

select convert(varchar(10),getdate(),120)

But I dont understand what you are going to do with QA result

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-24 : 09:42:54

Hi Peso

CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 126) as [launch-date] - This gives me what I wanted (i.e YYYY-MM-DD)

CONVERT(VARCHAR, CURRENT_TIMESTAMP, 126) as [launch-date], - This also gives me the required result but without '-' as mentioned in my first question(i.e 2007-08-24T14:47:55.743)

So I am happy with the first format and both queries works in SQL 2000.

Thank you

quote:
Originally posted by Zoroaster

quote:
Originally posted by Peso

Since when?

This my result in SSMS
2007-08-24T15:16:59.177

E 12°55'05.25"
N 56°04'39.16"

Fails in 2000, I didn't try in 2005.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources