Results 1 to 4 of 4

Thread: Convert GETDATE to yesterday 1st/last second

  1. #1
    Bill Turner Guest

    Convert GETDATE to yesterday 1st/last second

    In search of SQL to obtain "yesterday's" begin/end times for a batch process:

    GETDATE() needs to be converted to Yesterdays FIRST and LAST second:
    ------- --------------------------------
    2001-06-25 14:23:56.054 ---> 2001-06-24 00:00:00.001 (1st sec. of yesterday)
    2001-06-25 14:23:56.054 ---> 2001-06-24 23:59:59.999 (last sec. of yesterday)

    Will CAST and/or CONVERT fucntions assist? The follwoing SQL returns GETDATE()-1 (yesterday along w/ a time) I need the time converted to 1st and last second...

    declare @date_time_accessed datetime,
    @DateAccessed varchar(80),
    @TimeAccessed varchar(80)

    select convert(varchar(10),getdate()-1,120) as dateaccessed,
    convert(varchar(8),getdate()-1,114) as timeaccessed

  2. #2
    Ananth Guest

    Convert GETDATE to yesterday 1st/last second (reply)

    Bill,

    Do you need the first and last SECOND or MILLISECOND? Your example gives the millisecond?

    SQL Server stores milliseconds with a precision of 1/300ths of a second...in other words, milliseconds are stored as

    .000
    .003
    .007
    .010
    .013
    .017

    and so on
    It does NOT store values ending with 1,2,4,5,6,8 and 9.

    So the earliest time for a day would be
    2001-06-24 00:00:00.003

    and the latest would be

    2001-06-24 23:59:59.997

    Here's the code :


    select dateadd(ms, 3, convert(varchar, getdate()-1, 101)),
    dateadd(ms, -3, convert(varchar, getdate(), 101))




    - Ananth





    ------------
    Bill Turner at 6/25/01 9:57:30 AM

    In search of SQL to obtain "yesterday's" begin/end times for a batch process:

    GETDATE() needs to be converted to Yesterdays FIRST and LAST second:
    ------- --------------------------------
    2001-06-25 14:23:56.054 ---> 2001-06-24 00:00:00.001 (1st sec. of yesterday)
    2001-06-25 14:23:56.054 ---> 2001-06-24 23:59:59.999 (last sec. of yesterday)

    Will CAST and/or CONVERT fucntions assist? The follwoing SQL returns GETDATE()-1 (yesterday along w/ a time) I need the time converted to 1st and last second...

    declare @date_time_accessed datetime,
    @DateAccessed varchar(80),
    @TimeAccessed varchar(80)

    select convert(varchar(10),getdate()-1,120) as dateaccessed,
    convert(varchar(8),getdate()-1,114) as timeaccessed

  3. #3
    Guest

    Convert GETDATE to yesterday 1st/last second (reply)

    Ananth -- exactly what I needed. Thank you!

    Bill


    ------------
    Ananth at 6/25/01 11:02:05 AM

    Bill,

    Do you need the first and last SECOND or MILLISECOND? Your example gives the millisecond?

    SQL Server stores milliseconds with a precision of 1/300ths of a second...in other words, milliseconds are stored as

    .000
    .003
    .007
    .010
    .013
    .017

    and so on
    It does NOT store values ending with 1,2,4,5,6,8 and 9.

    So the earliest time for a day would be
    2001-06-24 00:00:00.003

    and the latest would be

    2001-06-24 23:59:59.997

    Here's the code :


    select dateadd(ms, 3, convert(varchar, getdate()-1, 101)),
    dateadd(ms, -3, convert(varchar, getdate(), 101))




    - Ananth





    ------------
    Bill Turner at 6/25/01 9:57:30 AM

    In search of SQL to obtain "yesterday's" begin/end times for a batch process:

    GETDATE() needs to be converted to Yesterdays FIRST and LAST second:
    ------- --------------------------------
    2001-06-25 14:23:56.054 ---> 2001-06-24 00:00:00.001 (1st sec. of yesterday)
    2001-06-25 14:23:56.054 ---> 2001-06-24 23:59:59.999 (last sec. of yesterday)

    Will CAST and/or CONVERT fucntions assist? The follwoing SQL returns GETDATE()-1 (yesterday along w/ a time) I need the time converted to 1st and last second...

    declare @date_time_accessed datetime,
    @DateAccessed varchar(80),
    @TimeAccessed varchar(80)

    select convert(varchar(10),getdate()-1,120) as dateaccessed,
    convert(varchar(8),getdate()-1,114) as timeaccessed

  4. #4
    Zaven Guest

    Convert GETDATE to yesterday 1st/last second (reply)

    Bill, you are not telling the purpose why do you need the date with first/last sec of the date. If you need to use it as a selection criteria to retrieve all records created for that day, for example if you want to select all records created from 2001-06-24 00:00:00.001 - 2001-06-24 23:59:59.999 here is how you do it without falling into sec/msec calcs.

    You specify
    SELECT ... where create_date >= 2001-06-24 (convert getdate() into date format. This date is being treated as 2001-06-24 00:00:00.000.) and create_date < 2001-06-25 i.e. [2001-06-24] + 1 as you noticed - (this date - &#34; < 2001-06-25&#34; is being treated as a last moment before 2001-06-25 00:00:00.000 arrives i.e. as 2001-06-24 23:59:59.999 that you are looking for.
    I think that&#39;s the correct way to approach this issue (if that&#39;s your case).

    SELECT ... where create_date >= 2001-06-24 and create_date < 2001-06-25

    Hope this helps. Let me know if you have any questions.

    Zaven

    ------------
    at 6/25/01 11:17:26 AM

    Ananth -- exactly what I needed. Thank you!

    Bill


    ------------
    Ananth at 6/25/01 11:02:05 AM

    Bill,

    Do you need the first and last SECOND or MILLISECOND? Your example gives the millisecond?

    SQL Server stores milliseconds with a precision of 1/300ths of a second...in other words, milliseconds are stored as

    .000
    .003
    .007
    .010
    .013
    .017

    and so on
    It does NOT store values ending with 1,2,4,5,6,8 and 9.

    So the earliest time for a day would be
    2001-06-24 00:00:00.003

    and the latest would be

    2001-06-24 23:59:59.997

    Here&#39;s the code :


    select dateadd(ms, 3, convert(varchar, getdate()-1, 101)),
    dateadd(ms, -3, convert(varchar, getdate(), 101))




    - Ananth





    ------------
    Bill Turner at 6/25/01 9:57:30 AM

    In search of SQL to obtain &#34;yesterday&#39;s&#34; begin/end times for a batch process:

    GETDATE() needs to be converted to Yesterdays FIRST and LAST second:
    ------- --------------------------------
    2001-06-25 14:23:56.054 ---> 2001-06-24 00:00:00.001 (1st sec. of yesterday)
    2001-06-25 14:23:56.054 ---> 2001-06-24 23:59:59.999 (last sec. of yesterday)

    Will CAST and/or CONVERT fucntions assist? The follwoing SQL returns GETDATE()-1 (yesterday along w/ a time) I need the time converted to 1st and last second...

    declare @date_time_accessed datetime,
    @DateAccessed varchar(80),
    @TimeAccessed varchar(80)

    select convert(varchar(10),getdate()-1,120) as dateaccessed,
    convert(varchar(8),getdate()-1,114) as timeaccessed

Posting Permissions

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