select from GETDATE()?

Question

select from GETDATE()?

obarahmeh

SSCommitted

Points: 1595
More actions
September 24, 2008 at 5:06 am

#96405

Dear All,
When you run the following command:
SELECT GETDATE() AS [CurrentDateTime]
The Result is : 2008-09-24 12:47:11.587
The question is: How can I get the following result from the above query:
20080924

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 102), '.', '')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

obarahmeh (9/24/2008)
Dear All,
When you run the following command:
SELECT GETDATE() AS [CurrentDateTime]
The Result is : 2008-09-24 12:47:11.587
The question is: How can I get the following result from the above query:
20080924

Where do you want to show/use formatted dates?

If it is only for display

SELECT convert(varchar(8),getdate(),112)

Madhivanan

Failing to plan is Planning to fail

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 3

Chris Morris (9/24/2008)
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 102), '.', '')

You dont need additional replace function

See my first reply 🙂

Madhivanan

Failing to plan is Planning to fail

obarahmeh SSCommitted Points: 1595 More actions · Answer 4

Thank you very much...Both of you correct.

Thanks again

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 5

obarahmeh (9/24/2008)
Thank you very much...Both of you correct.
Thanks again

Well. You didnt answer to my question 😉

Madhivanan

Failing to plan is Planning to fail

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Madhivanan, quite right! I don't know why the conversion I posted is being used - possibly from a previous version. It's used extensively here to generate a suffix for export filenames.

Thanks

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

obarahmeh SSCommitted Points: 1595 More actions · Answer 7

well, I have a trigger that reads the latest inserted data and insert them into another table. I've wrote the trigger, then it inserted all data from the first table, where I need only the daily data inserted. and so, I've added a condition where date = today date.

and I am checking that now.

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 8

obarahmeh (9/24/2008)
well, I have a trigger that reads the latest inserted data and insert them into another table. I've wrote the trigger, then it inserted all data from the first table, where I need only the daily data inserted. and so, I've added a condition where date = today date.
and I am checking that now.

If you want to find out today's data, use

wheredatecol>=dateadd(day,datediff(day,0,getdate()),0) and

datecol<dateadd(day,datediff(day,0,getdate()),1)

Madhivanan

Failing to plan is Planning to fail