Format Date

[ratzp]

What code we use to Convert 'dd/mm/yyyy' to SQL Format

 

[SQLDenis]

Should be just fine see example below

declare @d datetime
set @d ='01/24/2006'
select @d

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

SQL Server doesn't store dates and times in any 'format'. It stores those values as bytes. Refer to FAQ183-5834 for more information.

-SQLBill

Posting advice: FAQ481-4875

 

[SimonSellick]

You can use

convert(dateval, style)

to convert a date column or value to a display value; the particular one you asked for is style 103 (dd/mm/yyyy). You can also use this the other way around, to convert a string containing a date to a date value or column.

It is usually sensible to specify the style because the default behaviour for displaying and interpreting dates depends on the locale settings.

 

[SQLDenis]

Do you want to make '01/24/2006' a datetime format?

then do this
select convert(datetime,'01/24/2006')

What are you really trying to do here?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SimonSellick]

Oops, sorry - omitted the target data type. Should have been:

convert(varchar, dateval, style)

SQLDenis - The style-less conversion is ok with the value given, but not so good if the day and month are ambiguous - it gives different results depending on the DB or server settings.

 

[SQLDenis]

SimonSellick,

I am converting to datetime not to varchar, style has no function in this case

I don't really know what ratzp is trying to accomplish originally I had the impression that data inserts were faling and ratzp was trying to find a format that would succeed

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Actually style has function for varchar-> date conversions:

declare @d varchar(10)
set @d = '05/01/2005'

select convert(datetime, @d, 101)
select convert(datetime, @d, 103)

Kind of inline SET DATEFORMAT... this is "undocumented" (aka: not in BOL) CONVERT() feature we were talking about a month ago.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

Aha
I only tried 100,101,111 and 120 and it al returned the same
I did not try 103 but you are right

declare @d varchar(10)
set @d = '05/01/2005'

select convert(datetime, @d, 101)
select convert(datetime, @d, 110)
select convert(datetime, @d, 110)
select convert(datetime, @d, 120)


select convert(datetime, @d, 103)

I still don't see a use for this

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

declare @d varchar(10)
set @d = '05/01/2006'

-- style 100 crashes
select convert(datetime, @d, 101) -- m/d/y
select convert(datetime, @d, 104) -- d.m.y
select convert(datetime, @d, 105) -- d-m-y
select convert(datetime, @d, 110) -- m-d-y
select convert(datetime, @d, 120) -- ymd

Except for 120 (no delimiters), style behaves exactly as SET DATEFORMAT - delimiters don't matter.

I guess there is some use for this - in cases when input date string format is known and expected but environment (SET DATEFORMAT, SET LANGUAGE) is not. Plus SETs cannot be used within user-defined functions.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)