ints to date 1

[paulosinuk]

Hi,

I have a table with fields containing day, month and year which I'm trying to concaternate together into a cohesive date. Can't seem to find the syntax, can anyone help?

Thanks

Paulos

 

[manjarikosaraju]

SELECT convert(datetime,cast(month as varchar(2)) + '-' + cast(day as varchar(2)) + '-' + cast(year as varchar(4)),101)
FROM TABLE

 

[vongrunt]

This works... with two things to consider:

- format 101 is not necessary - it has purpose only when you convert from date to string
- overall it's better to use non-ambiguous date format (yy-mm-dd):

declare @y smallint, @m tinyint, @d tinyint
set @y = 2005
set @m = 4
set @d = 20

select convert(smalldatetime, convert(varchar(4), @y)+'-'+ convert(varchar(2), @m)+'-'+convert(varchar(2), @d))

Another way:

select dateadd(dd, @d-1, dateadd(mm, @m-1, dateadd(yy, @y-1900, 0)))

Damn, I hope Yukon will have DateSerial() or something.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[mrdenny]

Using the code that vongrunt posted will work fine.

declare @y smallint, @m tinyint, @d tinyint
set @y = 2005
set @m = 4
set @d = 20

select convert(smalldatetime, convert(varchar(4), @y)+'-'+ convert(varchar(2), @m)+'-'+convert(varchar(2), @d))

How ever as long as you build the string in a reconised date format sql will take it. It is a myth that the method you enter the date into SQL effects how the data is displayed. This is controlled by the date format in the convert statement or the collation type. You can test this by using vongrunts code above and this code.

declare @y smallint, @m tinyint, @d tinyint
set @y = 2005
set @m = 4
set @d = 20

select convert(smalldatetime, convert(varchar(2), @m)+'-'+ convert(varchar(2), @d)+'-'+convert(varchar(4), @y))

Both will give you the same output.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[JamesLean]

Both will give you the same output

True, but it might not always be the output you want! Try setting the language of your login to British English - both these statements will actually fail.

The interpretation of dates is controlled by the language of the current login and/or the setting of DATEFORMAT in the current batch. Each language has a default value for dateformat (dmy, mdy or ymd) but this can be overridden using SET DATEFORMAT. You can also change the language for the current batch using SET LANGUAGE.

Take the following examples. This shows the results of using different dateformats when using different languages/dateformst:

--sql server default (US) - MDY
SET LANGUAGE 'English'

SELECT CONVERT(smalldatetime, '20050420') --ok
SELECT CONVERT(smalldatetime, '2005-04-20') --ok
SELECT CONVERT(smalldatetime, '04-20-2005') --ok
SELECT CONVERT(smalldatetime, '20-04-2005') --fail

--British - DMY
SET LANGUAGE 'British English'

SELECT CONVERT(smalldatetime, '20050420') --ok
SELECT CONVERT(smalldatetime, '2005-04-20') --fail
SELECT CONVERT(smalldatetime, '04-20-2005') --fail
SELECT CONVERT(smalldatetime, '20-04-2005') --ok

--Japanese - YMD
SET LANGUAGE 'Japanese'

SELECT CONVERT(smalldatetime, '20050420') --ok
SELECT CONVERT(smalldatetime, '2005-04-20') --ok
SELECT CONVERT(smalldatetime, '04-20-2005') --ok
SELECT CONVERT(smalldatetime, '20-04-2005') --fail

As you can see, the only format that is consistently interpreted no matter what the language or dateformat is yyyymmdd.

format 101 is not necessary - it has purpose only when you convert from date to string

The style does have purpose when converting from string to datetime. These all work, regardless of the language settings:

SELECT CONVERT(smalldatetime, '20050420', 112)
SELECT CONVERT(smalldatetime, '2005-04-20', 120)
SELECT CONVERT(smalldatetime, '04-20-2005', 101)
SELECT CONVERT(smalldatetime, '20-04-2005', 103)

--James

 

[vongrunt]

Looks like I never used UK English settings so far :X

So technically SQL server splits date string into tokens (,-/ are delimiters) and attempts to compose date according to dateformat sequence (mdy or whatever). It there are no delimiters, it assumes ISO [yy]yymmdd format.

> The style does have purpose when converting from string to datetime.

Ack... hole in BOL documentation.

I think it's FAQ time... will finish it this week.

Nice explanation.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba