# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  Convert GETDATE to yesterday 1st/last second

## Bill Turner

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

----------


## Ananth

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

----------

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

----------


## Zaven

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

----------

