Access Dateserial function equivalent in SQL

I haven't been able to find the Dateserial function in SQL that corresponds to the one in Access.
thx
jmI don't think you can use a straigt forward command as
DateSerial(Year,Month,Day) in MS SQL.

You just have to join your colums and use the "convert" command in MS SQL.

MS SQL stores datetime formats in default by 'YYYYMMDD'.

so it would maybe be like this

convert ( datetime, @.columnYear+@.columnMonth+@.columnDay, 1).

Is there a better way? Anyone?|||Should work, assuming that @.columnYear+@.columnMonth+@.columnDay can be evaluated as string in the format YYYYMMDD. So make sure you have a leading '0' for month and days < 10. If you are not sure about that, you can enfore a leading zero by

RIGHT('0000'+@.columnYear, 4) + RIGHT('00'+@.columnMonth, 2) + RIGHT('00'+@.columnDay, 2)|||Does anyone test the code they post?

Patrick Chua SQL SERVER stores dates in either 2 4-byte integers or 2 2-byte integers not in YYYYMMDD format.

jmayo two suggestion:

-- =============================================
-- Create scalar function (FN)
-- =============================================
IF object_ID('dbo.DateSerial') is not null
DROP FUNCTION DateSerial
GO

CREATE FUNCTION DateSerial(
@.Year varchar(4)
, @.Month varchar(2)
, @.Day varchar(2))
RETURNS datetime
AS
BEGIN
declare @.Date datetime
select @.Date = convert(datetime, @.Year + '/' + @.Month + '/' + @.Day)
return @.Date
END
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.DateSerial('2003','8','15')
go

OR

declare @.columnYear varchar(4), @.columnMonth varchar(2), @.columnDay varchar(2)
select @.columnYear = '2003'
, @.columnMonth = '8'
, @.columnDay = '15'

select convert(datetime, @.columnYear + '/' + @.columnMonth + '/' + @.columnDay)|||Originally posted by Paul Young
Does anyone test the code they post?

Patrick Chua SQL SERVER stores dates in either 2 4-byte integers or 2 2-byte integers not in YYYYMMDD format.


Hi Paul, did you test my code? It works as fine as yours. But maybe do you have other regional settings. Actually, the style of the date format is as follows:

In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

- 0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mm:ss
- 9 or 109 (*) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 (*) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)

:rolleyes:|||DoktorBlue re-read the first two words in your reply... "Should work" I took that to mean Patrick's suggestion should work which it doesn't.

The style argument to CONVERT is used when converting datetime/smalldateime to character data or converting numeric data to character data NOT the other way around.

I do agree with you regarding leading zeros.|||Originally posted by Paul Young
DoktorBlue re-read the first two words in your reply... "Should work" I took that to mean Patrick's suggestion should work which it doesn't.

The style argument to CONVERT is used when converting datetime/smalldateime to character data or converting numeric data to character data NOT the other way around.

I do agree with you regarding leading zeros.

I hate to say this, but Paul you are wrong at all points:

1) It works at my PC, maybe your regional settings prevent you from getting the result, but here is the tested code:

SELECT convert(DateTime, '20030812'), convert(DateTime, '2003/08/12')

2) The style argument is both input (string --> datetime) as output (datetime --> char)! see this code demonstration:

declare @.D as datetime
set @.d= convert(datetime, '12.8.2003',104)
select @.d, convert(varchar(255), @.d, 7)
go

3) Leading zeros are only needed for an implicit format. See my example style 104, which does accept the 8 without a leading 0. So, this style would be a candidate for a more stable DateSerial procedure.

Cheers|||Yeah, Paul, cheer up, it all works, starting with Patrick's statement:

declare @.columnYear char(4), @.columnMonth char(2), @.columnDay char(2)
select @.columnYear = '1992', @.columnMonth = '01', @.columnDay = '15'
select convert ( datetime, @.columnYear+@.columnMonth+@.columnDay)
select convert(datetime, RIGHT('0000'+@.columnYear, 4) + RIGHT('00'+@.columnMonth, 2) + RIGHT('00'+@.columnDay, 2))

But the original question before the p***ing contest started was about DateSerial equivalent. I think Paul started good, except for taking VARCHAR's as parameters. How about changing them to INT's, Paul?|||jmayo, If I can be of help with your problem please post back OR contact me off line.|||Originally posted by Paul Young
jmayo, If I can be of help with your problem please post back OR contact me off line.

Hey Paul,

Knowledge is Power. Get Some!
Ain't you man enough to accept your shotcomings. We are all learning, right?|||Datetime is NOT stored in 'YYYYMMDD' format.

RIGHT('0000'+@.columnYear, 4) + RIGHT('00'+@.columnMonth, 2) + RIGHT('00'+@.columnDay, 2) fails for 2-digit year values unless you are living in the first century.

select @.Date = convert(datetime, @.Year + '/' + @.Month + '/' + @.Day) is the simplest, most direct approach.

Get off Paul's case.

blindman|||blindman: are you on a mission? What's its name?|||I want to thank all of you for your replies. I was unable to reply until now because of the power outage. I live in Michigan.
I've been using SQL for about 1 week so, I'm trying to learn on the fly.
I actually was using the Convert function just having trouble using it in the VIEW I'm creating. I'm trying to convert a Julian date. WHich I've been able to do over the years in other software packages, AS400QUERY, Access, Excel, etc... Thanks Again..
jm|||jmayo maybe you could post the code for your view and indicate the problem or error you are getting.

If you wish to convert a Julian date to a Gregorian date you can use:
declare @.JD int, @.i int, @.J int, @.k int
, @.l real, @.n int
set @.JD = 2440588
set @.L = @.JD + 68569
set @.N = 4 * @.L /146097
set @.L = @.L - (146097 * @.N + 3) / 4
set @.I = 4000 * (@.L+ 1) / 1461001
set @.L = @.L - 1461 * @.I / 4 + 31
set @.J = 80 * @.L / 2447
set @.K = @.L - 2447 * @.J / 80
set @.L = @.J / 11
set @.J = @.J + 2 - 12 * @.L
set @.I = 100 * (@.N - 49) + @.I + @.L

select @.j,@.k,@.i
select cast(@.j as varchar) + '/' + cast(@.k as varchar) + '/' + cast(@.i as varchar)
select convert(datetime,cast(@.j as varchar) + '/' + cast(@.k as varchar) + '/' + cast(@.i as varchar) )

courtisy of: http://aa.usno.navy.mil/faq/docs/JD_Formula.html|||This link has some sql code for converting to and from julian date format. You may have to change the constant for your system.

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=341&lngWId=5

blindman|||Once you get the year extrapilated you only need to add the day-1 of the year to most date functions to get the proper translation.
I should be all set now.
Thanks again|||Example QUASI-JULIAN DATE - 103001

CONVERT (datetime, LEFT (CONVERT (decimal, 1900 + dbo.DAILYSALE2.QSDGL / 1000), 4)) + CONVERT (decimal, RIGHT (dbo.DAILYSALE2.QSDGL, 3)) - 1

This seems to work as a formula within a view. QSDGL being the field that stores the QJDATE.Actually Decimal should be Integer in the convert parameter.
Anyway. Sorry to start all this contraversy.
thx