Getting dates and format string to date

tia_kamakshi
tia_kamakshi used Ask the Experts™
on
Hi Experts,

I am using MSSQL 2005

I wanted a list of all dates which exists on friday and saturday in year 2012 and 2013

Also, in varchar field I have date in following format "30/12/12", I wanted to convert these in date format and wanted to insert in datetime field


Please guide with query

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
try this, you can use any table/view you don't have to use sys.all_objects, just make sure it has enough rows to give the query the ability to count through both years

elect d from
(SELECT {ts '2012-01-01 00:00:00'} + row_number() over(order by object_id) - 1 d
FROM sys.all_objects) as x
where d between {ts '2012-01-01 00:00:00'}  and {ts '2013-12-31 00:00:00'}
and datepart(dw,d) in (6,7)
Most Valuable Expert 2011
Top Expert 2012

Commented:
for the second question...


convert(datetime,'30/12/12',3)
Top Expert 2011
Commented:
Please try:
-- Lists of all Fridays and Saturdays in 2012 and 2013
select dateadd(day, 7 * number,  '2012-01-06') FridaySaturday
from master.dbo.spt_values where type  = 'P'
and dateadd(day, 7 * number,  '1/6/2012') < '1/1/2014'
union
select dateadd(day, 7 * number,  '2012-01-07') FridaySaturday
from master.dbo.spt_values where type  = 'P'
and dateadd(day, 7 * number,  '1/6/2012') < '1/1/2014'
order by 1

Open in new window

-- Convert to DATETIME
select convert(datetime, '30/12/12', 3)

Open in new window

CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

HI,
For the dates here is the query:

DECLARE @d1 datetime, @d2 datetime
DECLARE @n int

 SELECT    
        @d1 = '20120101',
        @d2 = '20131231',
        @n = DATEDIFF(day,@d1,@d2)

 SELECT  
        Date,DATENAME(dw,date)
   FROM
      (
         SELECT TOP (@n)
                date = DATEADD( day,
                                ROW_NUMBER()OVER (ORDER BY t1.name)-1,
                                @d1)
           FROM
                sys.columns t1, sys.columns t2
      ) d
  WHERE
        DATENAME(dw,date)  IN ('Saturday','Friday')


For converting varchar to date use the following

select convert(datetime,<fieldname>,1)
Most Valuable Expert 2011
Top Expert 2012

Commented:
Here's another version, same idea as presented originally and in followups but this doesn't require a real table.  Just in-memory CTE's


WITH y(y)
     AS (SELECT '2012' y
         UNION ALL
         SELECT '2013' y),
     fs(d, y)
     AS (SELECT CONVERT(datetime, y + '0101') d, y FROM y
         UNION ALL
         SELECT dateadd(day, 1, d) d, y
           FROM fs
          WHERE d <= CONVERT(datetime, y + '0107')),
     x(d, y)
     AS (SELECT d, y
           FROM fs
          WHERE datepart(dw, d) IN (6, 7)
         UNION ALL
         SELECT dateadd(day, 7, d) d, y
           FROM x
          WHERE d <= CONVERT(datetime, y + '1231'))
SELECT   d
    FROM x
   WHERE d <= CONVERT(datetime, y + '1231')
ORDER BY d;

Commented:
SELECT * from tablename where DATENAME(dw, convert(datetime,'30/12/12',3)) in ('Friday','Saturday')

* replace '30/12/12' with your date field name

Author

Commented:
Many Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial