DATE Conversion Problem - 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 Conversion Problem

Author

Topic

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-01-08 : 04:29:15

I am using convert function for datetime values

but if i give their input as dd/mm/yyyy its shows conversion error
how can i rectify this

I am using like as below
SET @ConvertedFromDate = CONVERT(DATETIME,@FromDate,121)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-08 : 04:31:57

dd/mm/yyyy is style 103 use convert(datetime, @FromDate, 103)

KH
[spoiler]Time is always against us[/spoiler]

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-01-08 : 04:58:01

I tried 103 , Still it shows error...

Check this code:

DECLARE @Str DATETIME
SET @Str = '31/01/2008'
SELECT CONVERT(DATETIME,@Str,103)
Output:
Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(1 row(s) affected)

Any other way to do

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-08 : 05:07:31

[code]DECLARE @Str ~~DATETIME~~ varchar(10)
SET @Str = '31/01/2008'
SELECT CONVERT(DATETIME,@Str,103)[/code]

KH
[spoiler]Time is always against us[/spoiler]

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-01-08 : 05:30:42

Suppose if i give input in any format mm/dd/yyyy or dd/mm/yyyy or yyyy/mm/dd. I want output in one style. Is it possible to convert in some other format

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-08 : 05:46:28

quote:
Originally posted by karthickbabu

Suppose if i give input in any format mm/dd/yyyy or dd/mm/yyyy or yyyy/mm/dd. I want output in one style. Is it possible to convert in some other format

You should always express your dates as pure date values and not as characters. Use datetime parameter and assign value in universal format YYYYMMDD HH:MM:SS

Madhivanan

Failing to plan is Planning to fail

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-01-08 : 05:54:15

Ok Thank u madhivanan, But if i give input in this format dd/mm/yyyy , it shows error

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-08 : 07:37:29

quote:
Originally posted by karthickbabu

Ok Thank u madhivanan, But if i give input in this format dd/mm/yyyy , it shows error

Yes thats why I told you to use universal format
Are you sending in that format from front end application?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources