DATETIME formatting

DATETIME formatting

Post by Eduard de Vrie » Sat, 12 Jan 2002 22:14:36



Hi,

I have to write a "dynamic" query

I have a table (FINWEEK) containing dates and the corresponding internal
financial weeknr.
So: DATE, WEEKNR;
1/10,49;1/11,49;1/12,49;1/13,50;1/14,50;1/15,50;1/16,50;1/17,50;1/18,50;1/19
,50;1/20,51;1/21,51

On this table I want to look up the weeknr by using this query:
select WEEKNR from datweeknr.dbo.finweek where DATE = '1-11-2002'

That works, I now want to do the following query:
select WEEKNR from datweeknr.dbo.finweek where DATE = 'GETDATE()'
but this gives me the reply MM-DD-YYYY HH-MM-SS
I need a reply MM-DD-YYYY, is there any way to do this?

I tried decalring the GETDATE() as a variable but I cannot put it into a
substring, any easier solution?

Thanks in advance,

EdV

 
 
 

DATETIME formatting

Post by Hirantha S. Hettiarachch » Sat, 12 Jan 2002 22:19:02


Eduard,
look up convert finction on BOL.

--
hth
Hirantha S Hettiarachchi MCSD,MCDBA,OCP
----------------------------------------------------------------------------
-
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.



Quote:> Hi,

> I have to write a "dynamic" query

> I have a table (FINWEEK) containing dates and the corresponding internal
> financial weeknr.
> So: DATE, WEEKNR;

1/10,49;1/11,49;1/12,49;1/13,50;1/14,50;1/15,50;1/16,50;1/17,50;1/18,50;1/19
> ,50;1/20,51;1/21,51

> On this table I want to look up the weeknr by using this query:
> select WEEKNR from datweeknr.dbo.finweek where DATE = '1-11-2002'

> That works, I now want to do the following query:
> select WEEKNR from datweeknr.dbo.finweek where DATE = 'GETDATE()'
> but this gives me the reply MM-DD-YYYY HH-MM-SS
> I need a reply MM-DD-YYYY, is there any way to do this?

> I tried decalring the GETDATE() as a variable but I cannot put it into a
> substring, any easier solution?

> Thanks in advance,

> EdV



 
 
 

DATETIME formatting

Post by ibrahim kazanc » Sat, 12 Jan 2002 22:35:09


You could do that :
select cast(substring(Cast(getdate() as char),1,10) as datetime)

this will give you MM-DD-YYYY 00:00:00 which equal to MM-DD-YYYY.



Quote:> Hi,

> I have to write a "dynamic" query

> I have a table (FINWEEK) containing dates and the corresponding internal
> financial weeknr.
> So: DATE, WEEKNR;

1/10,49;1/11,49;1/12,49;1/13,50;1/14,50;1/15,50;1/16,50;1/17,50;1/18,50;1/19
> ,50;1/20,51;1/21,51

> On this table I want to look up the weeknr by using this query:
> select WEEKNR from datweeknr.dbo.finweek where DATE = '1-11-2002'

> That works, I now want to do the following query:
> select WEEKNR from datweeknr.dbo.finweek where DATE = 'GETDATE()'
> but this gives me the reply MM-DD-YYYY HH-MM-SS
> I need a reply MM-DD-YYYY, is there any way to do this?

> I tried decalring the GETDATE() as a variable but I cannot put it into a
> substring, any easier solution?

> Thanks in advance,

> EdV


 
 
 

DATETIME formatting

Post by ibrahim kazanc » Sat, 12 Jan 2002 22:38:49


Sorry it must be:
select cast(substring(Cast(getdate() as char),1,12) as smalldatetime)
 
 
 

DATETIME formatting

Post by Ilya Margoli » Sat, 12 Jan 2002 22:44:17


select WEEKNR from datweeknr.dbo.finweek where DATE = cast(cast(getdate() as
int) as datetime)



Quote:> Hi,

> I have to write a "dynamic" query

> I have a table (FINWEEK) containing dates and the corresponding internal
> financial weeknr.
> So: DATE, WEEKNR;

1/10,49;1/11,49;1/12,49;1/13,50;1/14,50;1/15,50;1/16,50;1/17,50;1/18,50;1/19
> ,50;1/20,51;1/21,51

> On this table I want to look up the weeknr by using this query:
> select WEEKNR from datweeknr.dbo.finweek where DATE = '1-11-2002'

> That works, I now want to do the following query:
> select WEEKNR from datweeknr.dbo.finweek where DATE = 'GETDATE()'
> but this gives me the reply MM-DD-YYYY HH-MM-SS
> I need a reply MM-DD-YYYY, is there any way to do this?

> I tried decalring the GETDATE() as a variable but I cannot put it into a
> substring, any easier solution?

> Thanks in advance,

> EdV


 
 
 

DATETIME formatting

Post by Eduard de Vrie » Sat, 12 Jan 2002 23:54:24


Wow Ibrahim,

was that a quick reaction or what?
Thanks!!

One aditional question, the statement:
select cast(substring(Cast(getdate() as char),1,12) as smalldatetime)

gives me as result MM-DD-YYYY HH:MM:SS
It took the milleseconds away.

How can I take the whole time away.
I am only looking for MM-DD-YYYY

Thanks in advance and have a great weekend,

Eduard

 
 
 

DATETIME formatting

Post by Eduard de Vrie » Sat, 12 Jan 2002 23:55:23


Hi Hirantha,

thanks for the reaction.
Quick question, what is BOL?

Thanks,

Eduard



Quote:> Eduard,
> look up convert finction on BOL.

> --
> hth
> Hirantha S Hettiarachchi MCSD,MCDBA,OCP
> --------------------------------------------------------------------------
--
> -
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.

 
 
 

DATETIME formatting

Post by Hirantha S. Hettiarachch » Sun, 13 Jan 2002 00:06:35


SQL server Books Online, its the complete documentation...

accrording to Zach,
"When in doubt, check out the Books Online,
  it's a GREAT source of information! "

 :-) !

--
hth
Hirantha S Hettiarachchi MCSD,MCDBA,OCP
----------------------------------------------------------------------------
-
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.



> Hi Hirantha,

> thanks for the reaction.
> Quick question, what is BOL?

> Thanks,

> Eduard



> > Eduard,
> > look up convert finction on BOL.

> > --
> > hth
> > Hirantha S Hettiarachchi MCSD,MCDBA,OCP

> --------------------------------------------------------------------------
> --
> > -
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.

 
 
 

DATETIME formatting

Post by sloa » Sun, 13 Jan 2002 02:33:27




..



Quote:> Hi,

> I have to write a "dynamic" query

> I have a table (FINWEEK) containing dates and the corresponding internal
> financial weeknr.
> So: DATE, WEEKNR;

1/10,49;1/11,49;1/12,49;1/13,50;1/14,50;1/15,50;1/16,50;1/17,50;1/18,50;1/19
> ,50;1/20,51;1/21,51

> On this table I want to look up the weeknr by using this query:
> select WEEKNR from datweeknr.dbo.finweek where DATE = '1-11-2002'

> That works, I now want to do the following query:
> select WEEKNR from datweeknr.dbo.finweek where DATE = 'GETDATE()'
> but this gives me the reply MM-DD-YYYY HH-MM-SS
> I need a reply MM-DD-YYYY, is there any way to do this?

> I tried decalring the GETDATE() as a variable but I cannot put it into a
> substring, any easier solution?

> Thanks in advance,

> EdV


 
 
 

DATETIME formatting

Post by Eduard de Vrie » Tue, 15 Jan 2002 19:20:30


Thanks people!!

You helped me out.

Eduard

 
 
 

1. Convert Aug 14 nvarchar format to 08/14/01 datetime format

I have a table that original came from a Word document, converted to Access,
now SQL.  I want to change an nvarchar field that has items like Aug 6 into
a datetime field that will result in 08/06/01.  How can I do that?

2. More Problems

3. DATETIME Format problem

4. New Btrieve developer page

5. Use of FOR XML EXPLICIT And ISO8601 DateTime Format

6. VACUUM ANALYZE FAILS on 7.0.3

7. What is correct BCP datetime format?

8. SQL-Mail

9. tsql datetime formatting

10. Adding language and changing DATETIME format

11. Datetime format configuration

12. Datetime format problems in a stored proc