date format

Question

Post reply

date format

arup chakraborty

Hall of Fame

Points: 3640
More actions
September 18, 2009 at 1:26 am

#96321

Hi,
I need to change the format of a date from '2009-09-18 12:54:59.860'
to '2009-09-18 00:00:00.000' (i.e. I want to keep the time format but with 00 values)
How is it possible?
Thanks in advance.

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

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

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 1

Try this link

http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

Clear Sky SQL
My Blog[/url]

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 2

Dave Ballantyne (9/18/2009)
Try this link
http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

Hi Dave,

It works fine. Even I have used the following code

declare @start_date varchar(23)

select @start_date = REPLACE((CONVERT (varchar(10), '2008-10-09 00:00:00.000',112) + ' 00:00:00.000'),'-','/')

print @start_date

But I feel ur the code described in the link is better than this.

Anyway,

I have another question.

If my datetime contains time part, the convert function is giving some error.

Like Fiscal_year (id int, start_date datetime, end_date datetime)

SELECT * FROM Fiscal_Year

WHERE CONVERT (varchar(10),'2009-09-01 00:00:00.000', 112)

BETWEEN CONVERT(varchar(10), start_date,112) AND CONVERT(varchar(10),(end_date - 1),112)

SELECT * FROM Fiscal_Year

WHERE '2009-09-01 00:00:00.000'

BETWEEN start_date AND (end_date - 1)

This two query gives different results. The second one is giving proper result though ideally both should return same result. Could you tell me what is the problem with CONVERT?

Dugi SSCoach Points: 17998 More actions · Answer 3

When you use the CONVERT function try to not determine the number of characters during conversations

try like this CONVERT(VARCHAR, DATE_FIELD, format)!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 4

Dugi (9/18/2009)
When you use the CONVERT function try to not determine the number of characters during conversations
try like this CONVERT(VARCHAR, DATE_FIELD, format)!

Hi Dugi,

There is no difference if we are not mentioning the number of characters

What 2 do?

Dugi SSCoach Points: 17998 More actions · Answer 5

Hmm wait a min;

SELECT * FROM Fiscal_Year

WHERE CONVERT (varchar(10),'2009-09-01 00:00:00.000', 112)

BETWEEN CONVERT(varchar(10), start_date,112) AND CONVERT(varchar(10),(end_date - 1),112)

SELECT * FROM Fiscal_Year

WHERE '2009-09-01 00:00:00.000'

BETWEEN start_date AND (end_date - 1)

'2009-09-01 00:00:00.000' - this must be column name no data, couz you are using in WHERE CLAUSE!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 6

Dugi (9/18/2009)
Hmm wait a min;
SELECT * FROM Fiscal_Year
WHERE CONVERT (varchar(10),'2009-09-01 00:00:00.000', 112)
BETWEEN CONVERT(varchar(10), start_date,112) AND CONVERT(varchar(10),(end_date - 1),112)
SELECT * FROM Fiscal_Year
WHERE '2009-09-01 00:00:00.000'
BETWEEN start_date AND (end_date - 1)
'2009-09-01 00:00:00.000' - this must be column name no data, couz you are using in WHERE CLAUSE!

No, actually thats a variable, start_date and end_date are the column names

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 7

Dugi (9/18/2009)
When you use the CONVERT function try to not determine the number of characters during conversations
try like this CONVERT(VARCHAR, DATE_FIELD, format)!

I''m digressing from the OP's date format conversion question, but in general I disagree with this advice. If you execute the following TSQL, you will find that the varchar is implicitly declared with a max length of 30 characters and so truncates the 50-character string stored in the local variable @S.

DECLARE @s-2 char(50)

SELECT @s-2 = '12345678901234567890123456789012345678901234567890'

SELECT CONVERT(varchar, @s-2), LEN(CONVERT(varchar, @s-2))

I think it is better practice to declare explicitly the length of all varchar and char data types to help avoid the possibility of "unexpected" string truncations.

Dugi SSCoach Points: 17998 More actions · Answer 8

Hmmm working with dates it works perfect so if we discus for any other converting you are right and I know that thing!

For more info about converting use this link

http://msdn.microsoft.com/en-us/library/aa226054%28SQL.80%29.aspx

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Fausto Echevarria SSC Eights! Points: 921 More actions · Answer 9

i think this is what you want..

select convert(datetime,convert(varchar,getdate(),112))

and you will get this...

-----------------------

2009-09-18 00:00:00.000

(1 row(s) affected)

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 10

Fausto Echevarria (9/18/2009)
i think this is what you want..
select convert(datetime,convert(varchar,getdate(),112))
and you will get this...
-----------------------
2009-09-18 00:00:00.000
(1 row(s) affected)

Hi Fausto,

That is exactly my wantsome. Thanks a ton.

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 11

Fausto Echevarria (9/18/2009)
i think this is what you want..
select convert(datetime,convert(varchar,getdate(),112))
and you will get this...
-----------------------
2009-09-18 00:00:00.000
(1 row(s) affected)

Hi Fausto,

That is exactly my wantsome. Thanks a ton.

Edward Boyle-478467 Ten Centuries Points: 1135 More actions · Answer 12

Declare @dt DateTime

Set @dt='20090604 09:00:00.123'

select convert(char(24),dateadd(dd,datediff(dd,0,@DT),0),121)

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 13

Hi Edward,

Another good way. Thanks.

But i feel

select convert(datetime,convert(varchar,getdate(),112))

is better and easier to understand.

But whtz abt performance?:w00t:

Fausto Echevarria SSC Eights! Points: 921 More actions · Answer 14

i see no problem with performance... but im not quite good at tuning and performance

i saw the execution plan and Client Statistics of these two queries

select MY_DateTime_Field from My_table

select convert(datetime,convert(varchar,MY_DateTime_Field,112)) from My_table

and i couldnt see any weird....

but im not sure if the execution plan and client statistics give me a good way to know if theres a poor performance or not.