Date formats in SQL

I need to compare a date field in a WHERE clause and I'm finding it very
difficult to do so.
I have a date stored in tables as integers like so:
20060317
etc
I need to compare todays date (less a day) to get the data out of the
table...so my query looks like this:
WHERE
IDPERI > 200601 and ididat =
case when UPPER(datename(dw,getdate())) = 'MONDAY'
then
cast(dateadd(dd,-3,getdate()) as int)
--and ididat < 20060320
else
cast(dateadd(dd,-1,getdate()) as int)
--and ididat < 20060320
end
However, the GetDate() statements between the then and else clauses return
the following:
2006-03-17 15:08:15.663
Now I just want the date to be yyyymmdd so I can compare. I have tried just
about everything I know and I cannot get the format to be yyyymmdd.
Thx
BillyBil, CAST your integer AS CHAR(8) il will workd fine.
A +
Billy a écrit :
> I need to compare a date field in a WHERE clause and I'm finding it very
> difficult to do so.
> I have a date stored in tables as integers like so:
> 20060317
> etc
> I need to compare todays date (less a day) to get the data out of the
> table...so my query looks like this:
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dw,getdate())) = 'MONDAY'
> then
> cast(dateadd(dd,-3,getdate()) as int)
> --and ididat < 20060320
> else
> cast(dateadd(dd,-1,getdate()) as int)
> --and ididat < 20060320
> end
> However, the GetDate() statements between the then and else clauses return
> the following:
> 2006-03-17 15:08:15.663
> Now I just want the date to be yyyymmdd so I can compare. I have tried jus
t
> about everything I know and I cannot get the format to be yyyymmdd.
> Thx
> Billy
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||SET @.DT_CURDATE = CAST(FLOOR(CAST(getdate() AS DECIMAL(15, 8))) AS DATETIME)
And then use @.DT_CURDATE instead of getdate()
Hope that helps
"Billy" wrote:

> I need to compare a date field in a WHERE clause and I'm finding it very
> difficult to do so.
> I have a date stored in tables as integers like so:
> 20060317
> etc
> I need to compare todays date (less a day) to get the data out of the
> table...so my query looks like this:
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dw,getdate())) = 'MONDAY'
> then
> cast(dateadd(dd,-3,getdate()) as int)
> --and ididat < 20060320
> else
> cast(dateadd(dd,-1,getdate()) as int)
> --and ididat < 20060320
> end
> However, the GetDate() statements between the then and else clauses return
> the following:
> 2006-03-17 15:08:15.663
> Now I just want the date to be yyyymmdd so I can compare. I have tried jus
t
> about everything I know and I cannot get the format to be yyyymmdd.
> Thx
> Billy|||On Mon, 20 Mar 2006 07:57:32 -0800, Billy wrote:

>I need to compare a date field in a WHERE clause and I'm finding it very
>difficult to do so.
>I have a date stored in tables as integers like so:
>20060317
Hi Billy,
Don't do this.
Change your DB. Store the date as a datetime or a smalldatetime. It'll
save you lots of headaches.
(snip)
>However, the GetDate() statements between the then and else clauses return
>the following:
>2006-03-17 15:08:15.663
>Now I just want the date to be yyyymmdd so I can compare. I have tried just
>about everything I know and I cannot get the format to be yyyymmdd.
Ugly kludge:
SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP, 112) AS int)
Hugo Kornelis, SQL Server MVP