Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2003
    Posts
    68

    Unanswered: 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
    jm
    jm

  2. #2
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    I 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?
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    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)
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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:

    Code:
    -- =============================================
    -- 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

    Code:
    declare @columnYear varchar(4), @columnMonth varchar(2), @columnDay varchar(2)
    select @columnYear   = '2003'
         , @columnMonth  = '8'
         , @columnDay    = '15'
    
    select convert(datetime, @columnYear + '/' + @columnMonth + '/' + @columnDay)
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    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)

    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    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
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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?

  9. #9
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    jmayo, If I can be of help with your problem please post back OR contact me off line.
    Paul Young
    (Knowledge is power! Get some!)

  10. #10
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    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?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    blindman: are you on a mission? What's its name?

  13. #13
    Join Date
    Aug 2003
    Posts
    68

    Thanks

    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
    jm

  14. #14
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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:
    Code:
    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
    Paul Young
    (Knowledge is power! Get some!)

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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...d=341&lngWId=5

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •