-
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
-
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
-
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
-
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 - " < 2001-06-25" 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's the correct way to approach this issue (if that'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'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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|