September 3, 2009 at 2:08 pm
Can someone tell me how to change this to accept English dates DD/MM/YY? It only works currently with MM/DD/YY and the output is odd as well.
********************
getbookings [08/01/09], [09/30/09]
********************
2009-08-01 00:00:00.000
********************
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[getbookings]
@datefrom varchar(8),
@dateto varchar(8)
as
WITH datecte(anydate) AS (SELECT CAST(@datefrom AS datetime) AS anydate
UNION ALL
SELECT anydate + 1 AS anydate
FROM datecte AS datecte_1
WHERE (anydate < CAST(@dateto AS datetime) - 1))
SELECT anydate
FROM datecte AS datecte_2
September 3, 2009 at 2:35 pm
maybe:
SET DATEFORMAT DMY
September 3, 2009 at 2:40 pm
No sorry, completely ignores it on both.
September 3, 2009 at 5:42 pm
Use the CONVERT function instead of CAST.
declare @datestring varchar(10)
set @datestring = '04/08/00'
select CONVERT(datetime,@datestring,3)
result = 2000-08-04 00:00:00.000
Look up the date styles table under CONVERT in books online. If your input contains the full four digits for year (dd/mm/yyyy), then you should use style 103 instead of style 3.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 5, 2009 at 4:16 am
Works a treat thanks.
My next problem is with an insert into a temp table. It doesn't like insert into #temp without the select.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[getbookings]
@datefrom varchar(8),
@dateto varchar(8)
as
--set @datefrom = convert (@datefrom, datetime, 103)
create table #temp (anydate datetime)
insert into #temp
WITH datecte(anydate) AS (SELECT CONVERT(datetime, @datefrom, 3) AS anydate
UNION ALL
SELECT anydate + 1 AS anydate
FROM datecte AS datecte_1
WHERE (anydate < CONVERT(datetime, @dateto, 3) - 1))
SELECT anydate
FROM datecte AS datecte_2
select * from #temp
drop table #temp
September 5, 2009 at 2:02 pm
The proper way to do an insert with a cte is like this:
;with cte (a,b,c) as (select whatever)
insert into #temp (x,y,z)
select a,b,c from cte
-- or
;with cte (a,b,c) as (select whatever)
select a,b,c
into #temp
from cte
For the future though, when the nature of the question changes, just put it in as a new question. Two reasons for this:
(1) People who search for similar problems might find it.
(2) Other volunteers who might have an answer will look into it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 5, 2009 at 2:41 pm
Thanks, will give that a go and take note of your comments.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply