jet sql to transacgt sql for datetime

[dlpastel]

I have an access database which will be ported over to sql server however, sql server seems to automatically store the date with the time as 07/09/2008 12:00:00:00

This is for a telephony program so the caller would punch touch tones of 070908

Normally my query for access would say

"select stats.* from stats
where datestat = 07/09/2008"

How would I do this with Tranact sql? I do not want it to look at the time portion of the date but only the date portion. I think I would have to use the convert function but I am not sure of the sql string.

 

[gmmastros]

First, realize that 07/09/2008 12:00:00:00 is how you SEE the data, not how it is stored. In SQL2008, there is a new data type called Date, which does not store the time component.

If your data does not have a time component, then your query will probably work (with some slight modifications).

With Access, the delimiter for dates is the # symbol. So your query really should have been:

[tt][blue]select stats.* from stats
where datestat = [!]#[/!]07/09/2008[!]#[/!][/blue][/tt]

With SQL Server, the date delimiter is '

So....

[tt][blue]select stats.* from stats
where datestat = [!]'[/!]07/09/2008[!]'[/!][/blue][/tt]

If the time component is midnight, then that query will work. However, if the data in your column has a time component, and you want to return all rows for a given day, it's better to do this...

[tt][blue]
select stats.*
from stats
where datestat >= '07/09/2008'
And datestat < '07/[!]10[/!]/2008'
[/blue][/tt]

Lastly, because of internationalization issues, it's best to use the ISO Unseparated Date Format (YYYYMMDD), like this...

select stats.* 
from   stats 
where  datestat >= '20080709'
       And datestat < '20080710'

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dlpastel]

Because of our hardware, I am not able to use Sql Server 2008. The hardware must run on Windows 2000 unless we do a software upgrade costing 30K. Therefore I must use Sql Server 2005 which does not have that date field. It is possible that the time element of each field might not always be 12:00:00:00. In that case is there a way to use a Sql statement that will only look at the date portion of the field?

 

[gmmastros]

There are various ways to ignore the time portion of a field. I already showed you one way (which happens to be the best way).

select stats.* 
from   stats 
where  datestat >= '20080709'
       And datestat < '20080710'

Notice that datestat must be greater than or equal to 20080709 (July 9, 2008) AND it must be less than 20080710 (July 10, 2008). This query will return all the rows for July 9, 2008. This query also has the benefit of being [google]sargable[/google]. This means, if there is an index on the datestat column, the query engine will be able to use the index to return the rows faster (better for performance).

SQL Server stored DateTime data (behind the scenes) as 2 integer values, one representing the date and another representing the time. However, it often helps to think of DateTime values and floats. The whole number represents the number of days that have elapsed since Jan 1, 1900 and the fractional part represents fractional days (0.5 = noon, 0.25 = 6 AM, 0.75 = 6 PM, etc...).

When you think of DateTime this way, many harder to understand concepts fall in to place.

My favorite method for removing the time component is to do a little date manipulation. SQL Server has a couple of functions that will help here. There's a DateDiff function that returns an INTEGER in whatever interval you specify. There's also a handy DateAdd function. Used together, you can remove the time component, like this...

[tt][blue]
[green]-- Current DateTime[/green]
Select GetDate()

[green]-- Integer Number of days that have elapsed since Jan 1, 1900[/green]
Select DateDiff(Day, '19000101', GetDate())

[green]-- Add the Whole number of days back to Jan 1, 1900[/green]
Select DateAdd(Day, DateDiff(day, '19000101', GetDate()), '19000101')
[/blue][/tt]


For another method, you can remove the time component by doing a little math manipulation. SQL Server has a FLOOR function that will round down to the nearest integer ( Floor(1.99) = 1 ). So, we can convert the DateTime value to Float, floor is, and convert back to datetime. This has the effect of removing the time component (setting it to 12:00:00).

Ex:

[tt][blue]

[green]-- Returns current Date/time[/green]
Select GetDate()

[green]-- Returns float representation[/green]
Select Convert(Float, GetDate())

[green]-- Removes fractional component[/green]
Select Floor(Convert(Float, GetDate()))

[green]-- Convert back to DateTime, no time component[/green]
Select Convert(DateTime, Floor(Convert(Float, GetDate())))
[/blue][/tt]

There's a third method, which involves string conversions. Strings, in any language, are slower than math manipulation, so I don't recommend this method.

[tt][blue]
[green]-- Current DateTime[/green]
Select GetDate()

[green]-- Convert with style argument = 112 (removes the time)[/green]
Select Convert(VarChar(20), GetDate(), 112)

[green]-- Convert back to DateTime[/green]
Select Convert(DateTime, Convert(VarChar(20), GetDate(), 112))
[/blue][/tt]

So, there you have 3 methods to remove the time component. You can use this in a query, like this...

select stats.* 
from   stats 
where  [!]DateAdd(Day, DateDiff(Day, 0, [/!]datestat[!]),0)[/!] = '20080709'

select stats.* 
from   stats 
where  [!]Convert(DateTime, Floor(Convert(Float, [/!]datestat[!])))[/!] = '20080709'

select stats.* 
from   stats 
where  [!]Convert(DateTime, Convert(VarChar(20), [/!]datestat[!], 112))[/!] = '20080709'

The problem with these 3 queries is that it is not sargable. If you have an index on the DateStat column, it will NOT be used by the query optimizer, and your performance will suffer. In fact, the only reason I mention it is because it helps to understand how DateTime values are handled.

You probably noticed the way I hardcode dates in all the queries. I am using the ISO Unseparated date format, which is "YYYYMMDD hh:mm:ss". You see, depending on the language of the login used to make the connection to SQL Server, dates are handled differently. Some areas of the world use mm/dd/yyyy and other places used dd/mm/yyyy. So, 3/4/2008 could represent March 4 or April 3, depending on your language. By using the ISO unseparated date format, there is no ambiguity, so SQL Server will always interpret it correctly.

I know there's a lot of information here. If there is anything you don't understand, please let me know and I will explain more. Properly handling dates is important. It's not hard, but there is plenty of opportunity to do it wrong. Once you understand the concepts I present here, most of your date handling code will make more sense.

I hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dlpastel]

Thanks for all the useful information. One last question. I have the opportunity at this point to handle dates any way I like, as datetime, smalldatetime or character fields since I am moving from Access to Sql Server. Is DateTime the way you would go?

 

[jbenson001]

Always use a datetime datatype for any datetime columns, don't use any type of character datatype.

 

[gmmastros]

Yes. Absolutely. DateTime. No hesitation.

The worst choice would be character field. If you do this, you will regret it. Eventually, everyone does.

SmallDateTime wouldn't be a bad choice. There are some limitations with SmallDateTime, but not many. The minimum date is Jan 1, 1900 and the max date is June 6, 2079. You cannot store seconds with a SmallDateTime. If you are willing to deal with these limitations, then SmallDateTime isn't a bad choice either. You save a couple bytes. DateTime takes 8 bytes to store where SmallDateTime only takes 4 bytes. Personally, I wouldn't let bytes per value be the basis of my decision.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom