Date format - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Date format

Author

Topic

jack_mike_2008
Starting Member

5 Posts

Posted - 2007-10-29 : 05:54:38

In database the date is stored in the following form
2002-11-26 10:34:43.850 in a datetime field.

I want date in format
YYYYMMDDhhmmss
20021126103443

I have tried the following two ways:

1.
SELECT replace(replace(replace( convert(varchar(20),create_dttm,120),'-',''),' ',''),':',''),
replace(replace(replace( convert(varchar(20),modif_dttm,120),'-',''),' ',''),':','')
FROM XXXXX

2.SELECT convert(varchar(8),create_dttm,112) + replace(convert(varchar(8),create_dttm,108),':',''),
convert(varchar(8),create_dttm,112) + replace(convert(varchar(8),modif_dttm,108),':','')
FROM XXXXX

Requirement:
Both the statement are giving the desired result. I have also done time checking and found that 2nd select is giving better result.

Is there any way to get the result in desired format without concatenating the columns or without using replace function i.e., any direct format.

I know that there are many experts and seniors who will help me. Thanks a lot in advance.

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 06:01:15

Best to do the formatting in the front end, rather than in SQL server.

What do you need this format for?

There are two ISO formats that SQL Server provides (in case either would do!):

yyyymmdd hh:mm:ss
yyyy-mm-ddThh:mm:ss

Kristen

jack_mike_2008
Starting Member

5 Posts

Posted - 2007-10-29 : 06:09:43

[quote]Originally posted by Kristen

Best to do the formatting in the front end, rather than in SQL server.

What do you need this format for?

There are two ISO formats that SQL Server provides (in case either would do!):

yyyymmdd hh:mm:ss
yyyy-mm-ddThh:mm:ss

The two suggested by you will have '-' / ':'. The format my client want is YYYYMMDDhhmmss. The data is going to be written to a text file so no front end option.

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 06:31:02

I would expect a single CONVERT to be quicker, i.e.:


SELECT	CONVERT(varchar(19), GetDate(), 121),	-- 19=len('yyyy-mm-dd hh:mm:ss')
	REPLACE(REPLACE(REPLACE(CONVERT(varchar(19), GetDate(), 121), '-', ''), ':', ''), ' ', '')

but you say
" In database the date is stored in the following form
2002-11-26 10:34:43.850 in a datetime field."
but you example shows two fields involved, separately for Date & Time???

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-29 : 06:36:48

or

select convert(varchar(8),getdate(),112)+replace(convert(varchar(10),getdate(),108),':','')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources