[Resolved] Convert DateTime to MMDDYYYY

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)

[Resolved] Convert DateTime to MMDDYYYY

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-03-30 : 16:36:26

Trying to convert datetime to format mmddyyyy

convert(varchar, bm.movement_date, 'mmddyyyy')

Error:
Argument data type varchar is invalid for argument 3 of convert function.

Thank you.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-30 : 16:48:53

look in SQL Help... it is expecting an integer... ie. 101 will give 'mm/dd/yyyy'...

Corey

I Has Returned!!

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-03-30 : 16:50:44

You need to define the style format to use - not specify the format you want. If you look in books online at the convert function, there is a table that shows you all of the possible formats.

For that specific format - you would use: convart(char(8), bm.movement_date, 101). That will get you the format with '/' so then you have to remove it. The final statement would be: replace(convert(char(8), bm.movement_date, 101), '/', '')

Jeff

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-03-30 : 16:51:40

You might find the following link helpful:
SQL Server 2005 Books Online
CAST and CONVERT (Transact-SQL)
http://msdn.microsoft.com/en-US/library/ms187928(v=SQL.90).aspx

CODO ERGO SUM

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-30 : 18:04:25

quote:
Originally posted by jeffw8713

<snip>
For that specific format - you would use: convart(char(8), bm.movement_date, 101). That will get you the format with '/' so then you have to remove it. The final statement would be: replace(convert(char(8), bm.movement_date, 101), '/', '')

Jeff

Small correction:

CAST(replace(convert(char(10), bm.movement_dat, 101), '/', '') AS CHAR(8))

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-03-30 : 20:57:56

Lamprey, right - thanks.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-03-31 : 08:51:27

Thank you very much, got it working.....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-01 : 05:51:35